Workbooks.Open Filename error

[Kurt]

I am getting the error Compile error: Syntax error on this line of code:

Set wbSource = Workbooks.Open Filename

the wbSource variable is declared.

What else might be going on?

Sub GetOpenFName()

    Dim FileName As Variant

    Dim Filt As String, Title As String

    Dim FilterIndex As Integer, Response As Integer

    Dim wbSource As Workbook

    Dim wbDest As Workbook


    Set wbDest = ActiveWorkbook
    

    '   Set to Specified Path\Folder

        On Error Resume Next

        ChDir ActiveWorkbook.Path

    '   Set File Filter

        'Filt = "Excel Files (*.xls, *.xlsx, *.xlsm, *.xlsb), *.xls, *.xlsx, *.xlsm, *.xlsb"

    '   Set *.* to Default

        'FilterIndex = 5

    '   Set Dialogue Box Caption

        Title = "Please select a different File"

    '   Get FileName

        FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)

    '   Exit if Dialogue box cancelled

        If FileName = False Then

            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")

            Exit Sub

        End If

    '   Display Full Path & File Name

        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")

    '   Open Selected Workbook

        Set wbSource = Workbooks.Open FileName
    
 

    'Copy from source workbook to dest workbook

       wbSource.Sheets("RawData").Range("A1:S29089").Copy wbDest.Sheets(“DataCalcs”).Range(“A1”)

 

End Sub

 

[Kurt]

Even when I add the file name I still get an error.

Set wbSource = Workbooks.Open FileName = "C:\\Documents"RawReports - All of Them - xxxx Copy"

 

[Fluff]

You need to put Filename in brackets

 

[Kurt]

I tried that and I am still getting an error do I just use Documents with Windows 10 now or is there a different syntax for the old My Documents?

Thanks for your first reply.

Set wbSource = Workbooks.Open FileName = "C:\\[Documents"RawReports - All of Them - xxxx Copy]"

 

[Fluff]

This line

Set wbSource = Workbooks.Open FileName

should be

Set wbSource = Workbooks.Open (FileName)

 

[Kurt]

This line

Set wbSource = Workbooks.Open FileName

should be

Set wbSource = Workbooks.Open (FileName)

This code is starting to work it opens the file dialog box and let's me select the file, but it is still not copying.

What else can I check for?

Sub GetOpenFName()

    Dim FileName As Variant

    Dim Filt As String, Title As String

    Dim FilterIndex As Integer, Response As Integer

    Dim wbSource As Workbook

    Dim wbDest As Workbook


    Set wbDest = ActiveWorkbook
    

    '   Set to Specified Path\Folder

        On Error Resume Next

        ChDir ActiveWorkbook.Path

    '   Set File Filter

        'Filt = "Excel Files (*.xls, *.xlsx, *.xlsm, *.xlsb), *.xls, *.xlsx, *.xlsm, *.xlsb"

    '   Set *.* to Default

        'FilterIndex = 5

    '   Set Dialogue Box Caption

        Title = "Please select a different File"

    '   Get FileName

        FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)

    '   Exit if Dialogue box cancelled

        If FileName = False Then

            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")

            Exit Sub

        End If

    '   Display Full Path & File Name

        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")

    '   Open Selected Workbook

       Set wbSource = Workbooks.Open("C:\\Documents\RawReports - All of Them - Tommy Copy")
    
 

    'Copy from source workbook to dest workbook

       wbSource.Sheets("RawData").Range("A1:S29089").Copy wbDest.Sheets("Documents\Data Calc Formulas v 1.13b\DataCalcs").Range("A3")

 

End Sub

 

[Fluff]

Firstly, remove this line

On Error Resume Next

all it does is mask any errors making it very difficult to debug.

Secondly, what is the point of all this code

    '   Set to Specified Path\Folder

        On Error Resume Next

        ChDir ActiveWorkbook.Path

    '   Set File Filter

        'Filt = "Excel Files (*.xls, *.xlsx, *.xlsm, *.xlsb), *.xls, *.xlsx, *.xlsm, *.xlsb"

    '   Set *.* to Default

        'FilterIndex = 5

    '   Set Dialogue Box Caption

        Title = "Please select a different File"

    '   Get FileName

        FileName = Application.GetOpenFilename(FileFilter:=Filt, FilterIndex:=FilterIndex, Title:=Title)

    '   Exit if Dialogue box cancelled

        If FileName = False Then

            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")

            Exit Sub

        End If

    '   Display Full Path & File Name

        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")

if you then go and hardcode the filepath/name into the workbook open?

Finally, this

Sheets("Documents\Data Calc Formulas v 1.13b\DataCalcs")

is not a valid sheet name

 

[Kurt]

I want the user to select the file first with that name.

 

[Fluff]

Please do not quote whole post as it just clutters up the thread.

Remove the on error line & make the change I suggested in post#5

 

[Kurt]

I did that still no luck.

Does this have anything to do with the UNC Universal Naming Convention and file paths on computer networks?