Open a sheet from an existing workbook

[gmazza76]

Good morning

I am trying to open a spreadsheet via vba to copy pieces of data into my current workbook.
I have a formula that changes the name based on the date etc.. that shows in the "telstats" reference below. (this changes daily)
I am getting an error on the "Set wb2" saying I have a syntax error, but I am unsure what I have missed as I normally use a static name instead of one that changes.

thanks in advance

Dim wb1 As Workbook, wb2 As Workbook
Dim telstats As String, Dim r As Range

Application.DisplayAlerts = False
Application.ScreenUpdating = False

telstats = Worksheets("Running System").Range("C9").Value

Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("C:\Users\" & Environ("username") & "\OneDrive - Verastar\" & telstats & "))
[code/]

 

[gallen]

I'm guessing the value in the 'telstats' variable is a workbook name.

Your issue is the last part.:

Without testing I think it should be:

Set wb2 = Workbooks.Open("C:\Users\" & Environ("username") & "\OneDrive - Verastar\" & telstats)

If it still has issues, let me know

 

[gmazza76]

that has worked, thank you.
Is there a way I can put a message if the file isnt found an error message i.e "file not open"

 

[dmt32]

Is there a way I can put a message if the file isnt found an error message i.e "file not open"

One way would be to use Dir Function to check filepath / Filename existence before trying to open it

something like

Dim wb1 As Workbook, wb2 As Workbook
    Dim telstats As String, FolderPath As String
    Dim r As Range
    

     With Application
        .DisplayAlerts = False: .ScreenUpdating = False
     End With
    
    Set wb1 = ThisWorkbook
    
    telstats = wb1.Worksheets("Running System").Range("C9").Value
    FolderPath = "C:\Users\" & Environ("username") & "\OneDrive - Verastar\"
    
    If Not Dir(FolderPath & telstats, vbDirectory) = vbNullString Then
    Set wb2 = Workbooks.Open(FolderPath & telstats, False, True)
    
    ' do copy stuff
    
    
    'close without saving
    wb2.Close False
    
    Else
    'inform user
        MsgBox FolderPath & telstats & Chr(10) & Chr(10) & "File Not Found", 48, "Not Found"
    End If
    
    With Application
        .DisplayAlerts = True: .ScreenUpdating = True
    End With

another way would be to trap the error generated when trying to open the file that cannot be found.

Dave

 

[gmazza76]

If I have the workbook I am copying from do I need to "Open" or can I just "activate" the other wb?

 

[gallen]

If I have the workbook I am copying from do I need to "Open" or can I just "activate" the other wb?

You can only "Activate" an open workbook. Ideally in code, you'd check the file is open first, if it isn't you open it