Solved Microsoft Visual basic Compile error

March 28, 2019 at 21:08:45
Specs: Windows 7
I have received this error message: with the line in red... Path = “C:\users\hewittl\desktop\"

Compile error: Expected: line number or label or statement or end of statement.

The macro is ...
Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = “C:\users\hewittl\desktop"
FileName1 = Range(“B5”)
FileName2 = Range(“B7”)
ActiveWorkbook.SaveAs filename:=Path & FileName1 & “_” & FileName2 & “.xlsm”, FileFormat:=xlNormal
End Sub

What have I done wrong

message edited by hewittl


See More: Microsoft Visual basic Compile error

Report •

#1
March 29, 2019 at 01:16:51
Don't use "Path" as a variable name. Rename it to something else -- pPath, or path, or filename, etc.

Report •

#2
March 29, 2019 at 01:26:38
Thanks for that.
I did try that but still received the same error message.

Private Sub CommandButton1_Click()
Dim Path As String
Dim FileName1 As String
Dim FileName2 As String
Path = “C:\users\hewittl\desktop\" - issue is here
FileName1 = Range(“B5”)
FileName2 = Range(“B7”)
ActiveWorkbook.SaveAs filename:=Path & FileName1 & “_” & FileName2 & “.xlsm”, FileFormat:=xlNormal
End Sub

It doesn't seem to like the location of where I want the file to be saved.


Report •

#3
March 29, 2019 at 04:35:38
✔ Best Answer
Look at the opening quotation mark. It looks like a quotation mark from word processing app or something like that. You've got a bunch of them in your code. They won't work in VBA.

Replace them all with a normal quotation marks, like the one following your Path name and see what happens. This type: "

You'll also get errors when the code hits the other further down in your code. You've got a bunch of them in there.

In addition, please click the following How-To link and read the instructions on posting VBA code in this forum. Thanks.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

Related Solutions

#4
March 29, 2019 at 06:21:08
@erilsiers

re: Don't use "Path" as a variable name.

While I am not recommending that Path be used as a variable name, it is not the cause of the compile error.

VBA will allow the user to set the Path and Filename using those keywords. This code works fine:


Sub OpenFile()

'Set Path and Filename
   Path = "Z:\myMainFolder\mySubFolder\"
   Filename = [A1]

'Open Workbook
     Workbooks.Open Path & Filename

End Sub

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Report •

#5
March 29, 2019 at 14:36:57
Thanks for that.

I re-typed it out and it worked.


Report •

#6
March 29, 2019 at 16:12:20
I'm glad you got it working.

Do you know how those quotation marks got there?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Report •

#7
March 29, 2019 at 18:30:00
I copied it from somewhere which seems to have had the wrong format.

When I typed it out, it was in the correct format and worked perfectly as it should have.

Will know for next time
Thanks again

message edited by hewittl


Report •

Ask Question