VBA - Link with variable-CodePudding

I am new to VBA and I want to add a link with a variable inside.

I have to open many files from an online sharepoint. The link changes every month as it needs to open a different file. So I need to create a variable inside the link so I can specify the link for each month.



Sub open_file_demo()
 
' declare variable
Dim pathname

Dim month As String
' assign a value
pathname = "https://sharepoint.com/sites/XXX/XXX/XXX/XXX/2022/06.xlsx?web=1"
 
' now open the file using the open statement
Workbooks.Open pathname
 
End Sub

So I want to have the filename (06.xlsx) to be declared from lets say A1 in the document

CodePudding user response：

dim var1 as string' your variable
pathname = "https://sharepoint.com/sites/" & var1 & "/2022/06.xlsx?web=1"

something like that. operate with link as with common string

CodePudding user response：

If I got you right you would like to read the filename (example 06.xlsx) from a certain cell in your worksheet and combine that with pathname.

You only need to read the value of the filename from cell A1 and concatenate that value with pathname in the proper way.

Sub open_file_demo()
 
    ' declare variables
    Dim pathname As String
    Dim filename As String

    Dim wks As Worksheet
    ' use the active sheet. Adjust accordingly
    Set wks = ActiveSheet
    
    'fill the filename with the value from A1
    filename = wks.Range("A1").Value

    
    ' concatenat pathname and filename
    pathname = "https://sharepoint.com/sites/XXX/XXX/XXX/XXX/2022/" & filename & "?web=1"
 
    ' now open the file using the open statement
    Workbooks.Open pathname
 
End Sub