Macro to Save As with filename based on cell contents.
I've been scouring the groups looking for a solution to this and I've
found many people with similar problems but none of the solutions proposed work for me. I'm sure it's my lack of familiarity with VBA and its implementation that's holding me back. Here's what I've got: I have and invoice with the invoice number in cell C7 and other data in A15. I want to run a macro that will save the current open document to, say, D:\Work\Accounting\Invoices. I want the file name to be: Invoice - [invoice number] - [other data].xls I tried methods along the lines of this: ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice - " & Worksheets("Service Invoice").Range("C7").Value & " - " & Worksheets("Service Invoice").Range("A15").Value & ".xls" but I get errors and no saved files. Once I get a working script I need to know in which module it goes. Can anyone lend a feller a hand? ]-[ |
Macro to Save As with filename based on cell contents.
Hi ImAFellow,
Try: '============= Public Sub TesterZ() Dim WB As Workbook Dim SH As Worksheet Dim sStr1 As String Dim sStr2 As String Const myPath As String = "D:\Work\Accounting\Invoices\" Set WB = ThisWorkbook Set SH = WB.Sheets("Service Invoice") sStr1 = SH.Range("C7").Value sStr2 = SH.Range("A15").Value WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _ & " - " & sStr2 & ".xls", _ FileFormat:=xlWorkbookNormal End Sub '<<============= This code should be pasted into a standard module. In the VBE: Menus | Insert | Module --- Regards, Norman wrote in message ups.com... I've been scouring the groups looking for a solution to this and I've found many people with similar problems but none of the solutions proposed work for me. I'm sure it's my lack of familiarity with VBA and its implementation that's holding me back. Here's what I've got: I have and invoice with the invoice number in cell C7 and other data in A15. I want to run a macro that will save the current open document to, say, D:\Work\Accounting\Invoices. I want the file name to be: Invoice - [invoice number] - [other data].xls I tried methods along the lines of this: ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice - " & Worksheets("Service Invoice").Range("C7").Value & " - " & Worksheets("Service Invoice").Range("A15").Value & ".xls" but I get errors and no saved files. Once I get a working script I need to know in which module it goes. Can anyone lend a feller a hand? ]-[ |
Macro to Save As with filename based on cell contents.
Thanks, Norman. I think I see the logic behind what you've done,
although I'm a bit confused by the syntax. I'll plug it in and report back. ]-[ Norman Jones wrote: Hi ImAFellow, Try: '============= Public Sub TesterZ() Dim WB As Workbook Dim SH As Worksheet Dim sStr1 As String Dim sStr2 As String Const myPath As String = "D:\Work\Accounting\Invoices\" Set WB = ThisWorkbook Set SH = WB.Sheets("Service Invoice") sStr1 = SH.Range("C7").Value sStr2 = SH.Range("A15").Value WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _ & " - " & sStr2 & ".xls", _ FileFormat:=xlWorkbookNormal End Sub '<<============= This code should be pasted into a standard module. In the VBE: Menus | Insert | Module --- Regards, Norman wrote in message ups.com... I've been scouring the groups looking for a solution to this and I've found many people with similar problems but none of the solutions proposed work for me. I'm sure it's my lack of familiarity with VBA and its implementation that's holding me back. Here's what I've got: I have and invoice with the invoice number in cell C7 and other data in A15. I want to run a macro that will save the current open document to, say, D:\Work\Accounting\Invoices. I want the file name to be: Invoice - [invoice number] - [other data].xls I tried methods along the lines of this: ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice - " & Worksheets("Service Invoice").Range("C7").Value & " - " & Worksheets("Service Invoice").Range("A15").Value & ".xls" but I get errors and no saved files. Once I get a working script I need to know in which module it goes. Can anyone lend a feller a hand? ]-[ |
Macro to Save As with filename based on cell contents.
Norman, when I run the script I get an error "400" with no explanation.
I've double- and triple-checked the myPath path (which is actually "D:\WORK\=ACCOUNTING\Invoices Pending", and not as I specified in my first post) and it checks out. So does the worksheet name. What could the problem be? ]-[ Norman Jones wrote: Hi ImAFellow, Try: '============= Public Sub TesterZ() Dim WB As Workbook Dim SH As Worksheet Dim sStr1 As String Dim sStr2 As String Const myPath As String = "D:\Work\Accounting\Invoices\" Set WB = ThisWorkbook Set SH = WB.Sheets("Service Invoice") sStr1 = SH.Range("C7").Value sStr2 = SH.Range("A15").Value WB.SaveAs Filename:=myPath & "Invoice - " & sStr1 _ & " - " & sStr2 & ".xls", _ FileFormat:=xlWorkbookNormal End Sub '<<============= This code should be pasted into a standard module. In the VBE: Menus | Insert | Module --- Regards, Norman wrote in message ups.com... I've been scouring the groups looking for a solution to this and I've found many people with similar problems but none of the solutions proposed work for me. I'm sure it's my lack of familiarity with VBA and its implementation that's holding me back. Here's what I've got: I have and invoice with the invoice number in cell C7 and other data in A15. I want to run a macro that will save the current open document to, say, D:\Work\Accounting\Invoices. I want the file name to be: Invoice - [invoice number] - [other data].xls I tried methods along the lines of this: ActiveWorkbook.SaveAs Filename:="D:\Work\Accounting\Invoices\Invoice - " & Worksheets("Service Invoice").Range("C7").Value & " - " & Worksheets("Service Invoice").Range("A15").Value & ".xls" but I get errors and no saved files. Once I get a working script I need to know in which module it goes. Can anyone lend a feller a hand? ]-[ |
Macro to Save As with filename based on cell contents.
Hi ImAFellow,
Norman, when I run the script I get an error "400" with no explanation.I've double- and triple-checked the myPath path (which is actually "D:\WORK\=ACCOUNTING\Invoices Pending", and not as I specified in my first post) and it checks out. So does the worksheet name. What could the problem be? I regret that I can only refer you to a post by Tom Ogilvy: http://tinyurl.com/kask3 --- Regards, Norman |
All times are GMT +1. The time now is 03:38 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com