You are here:

Excel/VBA how to refer to open files

Advertisement


Question
QUESTION: Hi Tom,
Using Excel 2007 VBA:
I need to write a macro which will be the only object in the workbook. When started, it prompts the user to find and open his “DataFile”. ColA contains file names, ColB contains account numbers, the remaining columns contain data. A twist here is that some files (ColA) can have more than one account number, like this:
FileName   AcctNum   Data1   Data2   Data3   Data4
cloudy   w1   data   data   data   data
rainy   x1   data   data   data   data
rainy   x2   data   data   data   data
rainy   x3   data   data   data   data
sunny   a1   data   data   data   data
sunny   a2   data   data   data   data
sunny   a3   data   data   data   data
sunny   a4   data   data   data   data

The macro will open cloudy.xls, copy data to it from DataFile.xls (there will be quite a bit of back and forth between these two files), then save and close cloudy.xls. Then it will open rainy.xls, copy data to it from DataFile.xls, then save/close rainy.xls. This continues until there are no more files in ColA. All files are in the same directory.
What I need help with is:
Each user’s “DataFile” will have a different name, so how do I give it a “temporary” name, or some such, a way to refer to it from within the code?
Also, how to find the next file name in ColA, that is not the same file the macro just finished with? And similarly, how do I refer to the file that’s opened from ColA ?
I sure hope this makes sense to you, as I’ve had a hard time constructing the question.
Thanks!

ANSWER: Doug,

I see three workbooks/worksheets

1)  the sheet you show with the file names.  I assume this is the activesheet when you start the macro.  In the code, this sheet is is referred to by the worksheet object shlist

2)  the datafile.  A file open dialog is presented and the user selects their data file.  This file is opened and a reference is set to it  bkData

3)  a file from the list (loop through the list - each file is opened once).  This is referenced with the workbook reference  bk

Sub abcd()
Dim shList As Worksheet
Dim r As Range, cell As Range
Dim fName As Variant
Dim bkData As Workbook
Dim bk As Workbook
Dim sPath As String
Dim s As String, olds As String
Dim shData As Worksheet
sPath = "D:\C_Drive\Data\"    ' make sure this ends with a back slash
Set shList = ActiveSheet
Set r = shList.Range("A2", shList.Cells(shList.Rows.Count, "A").End(xlUp))

ChDrive sPath
ChDir sPath
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls?),*.xls?", _
  Title:="Select Datafile", MultiSelect:=False)
If TypeName(fName) = "Boolean" Then

  MsgBox "No file selected.  Exiting . . . "

  Exit Sub

End If
Workbooks.Open fName
Set bkData = ActiveWorkbook
'Debug.Print bkData.Name
Set shData = bkData.Worksheets("Sheet1")
For Each cell In r
s = cell.Value & ".xlsx"
If s <> olds Then
 Set bk = Workbooks.Open(sPath & s)
' Debug.Print bk.Name
 bk.Close SaveChanges:=True
 olds = s
End If
Next
End Sub


Now you may have meant that the list of files to open is in the datafile workbook.  If so, then I will assume thatit is on the activesheet when you open the workbook.  The code would be

Sub abcd_eee()
Dim shList As Worksheet
Dim r As Range, cell As Range
Dim fName As Variant
Dim bkData As Workbook
Dim bk As Workbook
Dim sPath As String
Dim s As String, olds As String
Dim shData As Worksheet
sPath = "D:\C_Drive\Data\"    ' make sure this ends with a back slash

ChDrive sPath
ChDir sPath
fName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls?),*.xls?", _
  Title:="Select Datafile", MultiSelect:=False)
If TypeName(fName) = "Boolean" Then

  MsgBox "No file selected.  Exiting . . . "

  Exit Sub

End If
Workbooks.Open fName
Set bkData = ActiveWorkbook

Set shList = ActiveSheet
' to specify that sheet mydata be used for the file list
' set shList = bkData.Worksheets("Mydata")
Set r = shList.Range("A2", shList.Cells(shList.Rows.Count, "A").End(xlUp))

'Debug.Print bkData.Name
For Each cell In r
s = cell.Value & ".xlsx"
If s <> olds Then
 Set bk = Workbooks.Open(sPath & s)
' Debug.Print bk.Name
 bk.Close SaveChanges:=True
 olds = s
End If
Next
End Sub

Note also put in a commented line of code that will let you specify which sheet by name the list of files is on (comment out the   set shlist = Activesheet if you use that alternate command).

I have assumed that the filelist will start in cell A2 in both cases.

--
Regards,
Tom Ogilvy


---------- FOLLOW-UP ----------

QUESTION: Thanks, Tom
Your second scenario fits. I haven't tested it yet, but it looks like it will do just what I'm looking for. I understand it except for:
'Debug.Print bkData.Name
'Debug.Print bk.Name
What's that?

Answer
Doug,

Anything with a single quote at the beginning is a comment - not an executable line of code. It was executable but for my own purposes - so I commented it out.  You can delete it.

For me it is how I check my code.  I am old school (maybe just old) - no watch windows and all that other debug stuff.  So I checked my code to make sure it worked although I modified a line or two after I put it in the response - always a bad idea - but hopefully I didn't mess up the tested code.  

--
Regards,
Tom Ogilvy

About Excel
This topic answers questions related to Microsoft Excel spreadsheet (or workbook) stand-alone or Mircrosoft Office Excel including Excel 2003, Excel 2007, Office 2000, and Office XP. You can get Excel help on Excel formulas(or functions), Excell macros, charting in Excel, advanced features, and the general use of Excel. This does not provide a general Excel tutorial nor the basics of using a spreadsheet. It provides specific answers to using Microsoft Excel only. If you do not see your Excel question answered in this area then please ask an Excel question here

Excel

All Answers


Answers by Expert:


Ask Experts

Volunteer


Tom Ogilvy

Expertise

Selected as an Excel MVP by Microsoft since 1999. Answering Excel questions in Allexperts since its inception in 2001. Able to answer questions on almost all aspects of Excel's internal capabilities. If seeking a VBA solution, please specify that in your question itself so I give you the answer you want. [Excel has weak protection - if you are distributing an application, I don't answer questions on how to protect your project from your users.]

Experience

Extensive experience.

Education/Credentials
Master of Science (MS) degree Operations Research (ORSA)

Awards and Honors
Microsoft MVP in Excel.

©2016 About.com. All rights reserved.