VBA - Link with variable

Question

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

Storax · Accepted Answer · 2022-06-23 11:31:38Z

2

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

answered Jun 23, 2022 at 11:31

Storax

12.7k3 gold badges21 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

IvanSTV · Accepted Answer · 2022-06-23 11:25:43Z

0

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

answered Jun 23, 2022 at 11:25

IvanSTV

3642 silver badges13 bronze badges

Collectives™ on Stack Overflow

VBA - Link with variable

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related