Why can't I get a file to save in a specific place?

Question

I am using the code below to insert a number from a .txt file, "save-as" the word file using that number as the name, then completing a mail merge.

The code worked fine until I had to switch computers, so file locations changed...I obviously changed the locations to match those of the new computer.

Now, the number is inserted in the correct place, then the save dialog box opens but it doesn't put it in the right location nor does it insert the specified number.

It tries to save it here no matter what I change the file path to be: C:\Users\Schlechter Ag Liquid\OneDrive\BOLTemplate\

Sub CommandButton1_Click()


Invoice = System.PrivateProfileString("C:\Users\Schlechter Ag Liquid\OneDrive\BOLTemplate\" & _
    "invoice-number.txt", "InvoiceNumber", "Invoice")

If Invoice = "" Then
    Invoice = 1
Else
    Invoice = Invoice + 1
End If

System.PrivateProfileString("C:\Users\Schlechter Ag Liquid\OneDrive\BOLTemplate\" & _
    "invoice-number.txt", "InvoiceNumber", "Invoice") = Invoice

' Insert the number in the document
ActiveDocument.Bookmarks("Invoicenan").Range.InsertBefore Format(Invoice, "")

ActiveDocument.SaveAs FileName:= _
 "C:\Users\Schlechter Ag Liquid\OneDrive\BOLs\" & Format(Invoice, "") & ".docx"

ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
    ActiveDocument.MailMerge.OpenDataSource Name:= _
        "C:\Users\Schlechter Ag Liquid\OneDrive\BOLTemplate\Customer Database.accdb" _
        , ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
        AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
        WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
        Format:=wdOpenFormatAuto, Connection:= _
        "Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=C:\Users\Schlechter Ag Liquid\OneDrive\BOLTemplate\Customer Database.accdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Engine Type=6;Jet OLE" _
        , SQLStatement:="SELECT * FROM `report1 (1)`", SQLStatement1:="", SubType _
        :=wdMergeSubTypeAccess
    ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
     WordBasic.MailMergeFindEntry
End Sub

It says that the error is in this line, but I can't seem to figure out what is wrong with it.

ActiveDocument.SaveAs FileName:= _
 "C:\Users\Schlechter Ag Liquid\OneDrive\BOLs\" & Format(Invoice, "") & ".docx"

Any help would be greatly appreciated.

You say it '...doesn't put it in the right location...' - does it save it anywhere? if so what is the full path? The syntax of the save is correct. — Wayne G. Dunn, Dec 16 '15 at 05:06
Did you have spaces in filename before? I'm not sure if VBA manages spaces in path rightly. Maybe you could try `"""C:\Users\Schlechter Ag Liquid\OneDrive\BOLs\" & Format(Invoice, "") & ".docx"""`? — Vincent G, Dec 16 '15 at 08:39
@VincentG after substituting your code for mine, nothing changed. — Ellen Schlechter, Dec 16 '15 at 19:30

score 0 · Answer 1 · answered Dec 18 '15 at 09:37

Why do you Format the Invoice?? If you want to make a string out of it why don't you just set CStr(Invoice) instead of Format? And if you add an Integer to a string it normally converts itself to a string automatically... I would advise you to get rid of the format and just leave Invoice there. So:

ActiveDocument.SaveAs FileName:= _
    "C:\Users\Schlechter Ag Liquid\OneDrive\BOLs\" & Invoice & ".docx"

at least you can try it... If it doesn't work, well then I'm sorry :)

EDIT: It might be possible that I missunderstood a part of your code. The file itself should be called "invoice-1.docx"? If so you need to change something:

Invoice = "Invoice" & 1

Then you could use the other code i wrote ;)

Why can't I get a file to save in a specific place?

1 Answers1