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

 

[Fluff]

What happened?
Did you get any error messages?
Did the msgbox show you the correct file?
Did the selected file open?

 

[Kurt]

What happened?
Did you get any error messages?
Did the msgbox show you the correct file?
Did the selected file open?

No, it does not give an error message at all.

It confirms the selection but when I try to press Ctrl V there is nothing there.

Thank you for your efforts!!

 

[Fluff]

You didn't answer my last question.
Also what do you mean by

when I try to press Ctrl V there is nothing there.

 

[Kurt]

It opens the file dialog box, then it let's me select the file, but I do not think it is copying anything.

I go the worksheet I want to copy the info to and press Ctrl V for a paste to see if it got anything in memory and it did not.

 

[Fluff]

You have still not answered my last question
Did the selected file open?

 

[Kurt]

You have still not answered my last question
Did the selected file open?

Yes the file opened, but nothing was copied.

 

[Fluff]

If you removed the On Error line from you code, it suggests that there is no data on the "RawData" sheet in the newly opened workbook.

 

[Kurt]

OK now we are starting to get somewhere.

I get the following error message:

Run time error '1004'
Sorry, we couldn't find C:\Documents\RawReports All of Them - xxxx Copy.xlsx. Is it possible it was moved, renamed or edited?

 

[Fluff]

What if you use this

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

        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

 

[Yongle]

For the sake of continuity, this is from your now closed duplicated thread

Yongle posted this and asked Kurt to run it

Sub IsFileThere()
    Dim msg As String, fPath As String
    fPath = "C:\Documents\Data Calc Formulas v 1.13b.xlsb"

    If Dir(fPath, vbDirectory) = vbNullString Then msg = "NOT "
    MsgBox "File " & fPath & vbCr & msg & "FOUND"        
End Sub

Kurt replied

Thanks for this snippet of code Yongle.
I get the error message the file is not found!
I have tried several iterations of the file name path
What else can I check for?

___________________________________________________________________________________

The way forward?

1. You do not get an error message, you get a message box telling you that the full filepath and filename are not found - it is not found because they do not exist (either or both or the combination is incorrect)

2. "I have tried several iterations of the file name path" - random guessing will not work, VBA is literal in the extreme

3 To get the ONLY path that will work
- using File Explorer find the file
- right click on it
- click Properties
- select Security tab
- look for Object Name (near the top)
- select the full path (including the name) and copy it to your code

4 Try running your code again