CODEWITHSUNDEEP
excelvba
akh275
akh275

Reputation: 15

How do I assign a open workbook and worksheet to variables?

Trying to create a script that will open a directory, select the file and assign the workbook name and worksheet name to variables. When the workbook opens the file, I'm trying to get VBA to assign that file to a variable along with the first worksheet.

Workbook Variable: DataFile_Workbook
Worksheet Variable: DataFile_Sheet

Currently my code is :

Sub OpenWorkbooksAndChangeNames
Workbooks.open("\\datafiles\*.xlsx" <- this opens the file in the directory

Dim wb As Workbook
Dim ws As Worksheet
**Set wb = DataFile_Workbook
Set ws = DataFile_Sheet**  <- this is where im trying to assign the workbook name open from line 2 to "DataFile_Workbook" and "DataFile_Sheet" as the first worksheet name.

Upvotes: 0

Views: 3058

Answers (1)

braX
braX

Reputation: 11755

Set the object when you open it. You will need the know the filename from the wildcard, so use the Dir function for that. Note the Dir function returns only a filename and not a path for it.

Dim wb As Workbook
Dim ws As Worksheet
Dim sFilename As String
sFilename = Dir("\\datafiles\*.xlsx")
Set wb = Workbooks.Open("\\datafiles\" & sFilename)
Set ws = wb.Worksheets(1)

'assign them to variables (given your example, there is no need to do this)
Dim DataFile_Workbook As String
DataFile_Workbook = wb.Name
Dim DataFile_Sheet As String
DataFile_Sheet = ws.Name

' show the names 
MsgBox DataFile_Workbook ' or just use wb.Name
MsgBox DataFile_Sheet    ' or just use ws.Name

Understand that if there is more than one file in that folder that matches the wildcard, it will just open the first one it finds no matter how many there are.

If you know the filename....

Set wb = Workbooks.Open("\\datafiles\" & DataFile_Workbook)

Upvotes: 2

Related Questions