Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook.SaveAs not saving correctly
Hi guys,
Once again I ask for assistance, however I'm getting much better at this vba. Just not that good yet ;) Anyway, I'm writing an invoice for my nephew [electrical contractor] with a macro in that will automatically save the workbook with a new name. He opens a blank template and when he's filled in the details he hits the save macro button and the file should save as a new book with a unique filename generated from his invoice number joined with the first 4 letters of his customers surname. The code I've used is.. Sub SaveUnique() Dim fName As String fName = Range("m3").Value ThisWorkbook.SaveAs Filename:=fName End Sub Pretty simple looking and it did actually save the first couple in the same folder as the template. However it now wants to only save in My Documents. The folder it should be saving in is MyDocuments\invoices. If I open the template from the folder MyDocuments\invoices it saves to My Documents only now, after having initially saved to the correct folder twice. The vba help states that the SaveAs method save to the current folder, but it's not happening for me. Any Ideas? Regards, Ron |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook.SaveAs not saving correctly
Try something like:
ThisWorkbook.SaveAs Filename:=fMyDocs & fName Where fMyDocs might be initialised as: fMyDocs = Range("m2").Value 'cell M2 = C:\Documents and Settings\MyUserName\My Documents\ or fMyDocs = Environ("USERPROFILE") & Application.PathSeparator & "My Documents" & Application.PathSeparator -- Rob van Gelder - http://www.vangelder.co.nz/excel "Ron" wrote in message 10.130... Hi guys, Once again I ask for assistance, however I'm getting much better at this vba. Just not that good yet ;) Anyway, I'm writing an invoice for my nephew [electrical contractor] with a macro in that will automatically save the workbook with a new name. He opens a blank template and when he's filled in the details he hits the save macro button and the file should save as a new book with a unique filename generated from his invoice number joined with the first 4 letters of his customers surname. The code I've used is.. Sub SaveUnique() Dim fName As String fName = Range("m3").Value ThisWorkbook.SaveAs Filename:=fName End Sub Pretty simple looking and it did actually save the first couple in the same folder as the template. However it now wants to only save in My Documents. The folder it should be saving in is MyDocuments\invoices. If I open the template from the folder MyDocuments\invoices it saves to My Documents only now, after having initially saved to the correct folder twice. The vba help states that the SaveAs method save to the current folder, but it's not happening for me. Any Ideas? Regards, Ron |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
ThisWorkbook.SaveAs not saving correctly
Many Thanks Rob.
I went for the Environ("USERPROFILE") method, works great. Thanks again. Ron "Rob van Gelder" wrote in : Try something like: ThisWorkbook.SaveAs Filename:=fMyDocs & fName Where fMyDocs might be initialised as: fMyDocs = Range("m2").Value 'cell M2 = C:\Documents and Settings\MyUserName\My Documents\ or fMyDocs = Environ("USERPROFILE") & Application.PathSeparator & "My Documents" & Application.PathSeparator |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Fill colors not saving correctly. They change color upon reopening | Excel Discussion (Misc queries) | |||
activeworkbook.saveas - saving format changes | Excel Programming | |||
Canceling SaveAs dialog box without saving workbook | Excel Programming | |||
Disabling Saveas and saving file with a specific name only | Excel Programming | |||
SaveAs method fails when saving to FTP site. | Excel Programming |