Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Open File Using Variable in Excel Macro

0

Can someone tell me how to open a file from within a macro where the file name needs to be a variable? Currently using

Workbooks.Open (ThisWorkbook.path & "\Ridgemark 12-1-17.xls")

but want to use a variable in place of the "Ridgemark 12-1-17" file name.

Post Edited
Title: Title was not descriptive.
Answer
Discuss

Answers

0
Selected Answer
Sub fileOpen()

'Name of the file within quotation marks
yourVariable = "test.xlsx"

'Open the file
Workbooks.Open (ThisWorkbook.Path & "\" & yourVariable)

End Sub

Discuss

Discussion

The answer resolved the issue however another related issue arose later in the macro. Again it is how to use the variable, "iNewFile", to activate the open file.
The code line that fails is [Code]Windows(iNewFile).Activate"[\Code]. The error is Run-time error '9': Subscript out of range. More assistance would be greatly appreciated. Thanks
NormZ (rep: 2) Dec 18, '17 at 5:49 pm
Basically, you are not actually telling Excel what to activate due to a number of potential reasons. Post a new question with more/all of the code and it will be easier to help. Also, just click the CODE tags button to add the code when you make a post and it will then work.
don (rep: 1989) Dec 18, '17 at 5:59 pm
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login