locked
Compile Error - Syntax Error Excel 2010 VBA RRS feed

  • Question

  • Hello,

    I'm writing a macro which among other things will run a vlookup from a daily file labeled "APODaily" to the main file labeled "MainWkbk". I start with the MainWkbk then am trying to open the APODaily file from a shared drive. I recorded the macro which work, but I am trying to cleanup the code so that it's allows for the changing date at the end of the APODaily file name.  Ultimately I'd like to install a form so users can push a button re-running the vlookup at any date but that's another post.

    Originally I kept getting "Expected End Of Statement" error, but I corrected that, now I'm getting "Compile Error - Syntax Error". The error comes up on the line of code Workbooks.open(APODaily), _". What am I doing wrong?

    Dim MainWkbk As Workbook
    Dim APODaily As String
    Sub APOarStatus()
    Set MainWkbk = ActiveWorkbook
    Application.DisplayAlerts = False
    Dim Path As String
    Path = "S:\Daily Reports\APO\"
    Dim PrevDate As Date
    PrevDate = Date - 1
    APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls" _
        Workbooks.open(APODaily), _ <<<<<<<<<Error pops up on this line>>>>>>
        Notify:=False, ReadOnly:=True
        
    Application.DisplayAlerts = True


    Lorac1969

    Tuesday, June 23, 2015 3:14 PM

Answers

  • I know it,

    But you can´t do this.

    better saying,

    You don´t need him.

    APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls"

    You are setting a value to the APODaily variable. This command finish at ".xls"

    Underscore is used to break the same command in two or more lines, but here we are talking of just one command.

    If I understood properly your problem

    Tuesday, June 23, 2015 5:19 PM

All replies

  • APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls"
    
        Workbooks.Open (APODaily), _
            Notify:=False, ReadOnly:=True
                

    There is no underline at the end of the first instruction.

    Are two separated commands.

    APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls"

    other line

        Workbooks.Open (APODaily), _
            Notify:=False, ReadOnly:=True

    • Proposed as answer by André Santo Tuesday, June 23, 2015 3:54 PM
    Tuesday, June 23, 2015 3:54 PM
  • Hi Andre,

    Thanks for taking the time to respond. There actually is an underscore at the end of the first line, I think you just missed it when copying my code.


    Lorac1969

    Tuesday, June 23, 2015 5:00 PM
  • I know it,

    But you can´t do this.

    better saying,

    You don´t need him.

    APODaily = Path & "AP_Daily_SNP_" & Format(PrevDate, "mmddyy") & ".xls"

    You are setting a value to the APODaily variable. This command finish at ".xls"

    Underscore is used to break the same command in two or more lines, but here we are talking of just one command.

    If I understood properly your problem

    Tuesday, June 23, 2015 5:19 PM