Get names of closed workbooks?

[Vet Tech]

I'm a relative newbie at VB but I've managed to cobble together bits
of code gleaned from this NG which enables me to extract selected
data from a group of closed workbooks located in a particular folder
( located at "C:\Temp\Survey) and then save it to another workbook
called Update.xls, in worksheet called Get Data. This is based on two
macros - the most significant of which is based on a development
originally done by Nikos Yannacopoulos.

I want to write an extra couple of lines that will get the name of
each closed workbook in the folder. Here is the code for both macros.
the latter one has been shortened so that it shows the principle
involved. Here is the code:-

Private Sub Process_All_WorkBooks_Click()

' Process_All_Workbooks Macro
' This Macro will open all spreadsheets in a particular folder.
' It will then run another Macro
' Macro created by Nikos Yannacopoulos.


Dim fs, f, f1
Dim strFile As String
fldr = "C:\Temp\Survey\" 'Put your folder path here.
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile


Call Get_Data(f2.Name) 'Call another macro or write code to do
what youwant here.

Windows(f2.Name).Activate
ActiveWorkbook.Close savechanges:=False 'Change accordingly.
True will save the spreadsheet and False will not.

End If
Next
Application.ScreenUpdating = True
End Sub


Private Sub Get_Data(ExcelFileName As String)

Workbooks(ExcelFileName).Worksheets("Working").Range("L2:L2").Copy
Workbooks("Update.xls").Worksheets("Get Data").Range("E65536").End
(xlUp).Offset(1).PasteSpecial
End Sub

Any help would be much appreciated.
VT

 

[Jim Cone]

Re: "I want to write an extra couple of lines that will get the name of each closed workbook in the folder."

I would add this line just below the other "Dim" lines.
Dim fldr, f2, i

'Then change this line in the second sub...
Workbooks("Update.xls").Worksheets("Get Data").Range("E65536").End(xlUp).Offset(1).PasteSpecial

'to...
With Workbooks("Update.xls").Worksheets("Get Data").Range("E65536").End(xlUp)
.Offset(1).PasteSpecial
.Offset(1, 1).Value = ExcelFileName
End With
'--
The above assumes Column F is blank on the "Get Data" worksheet.
--
Jim Cone
Portland, Oregon USA




"Vet Tech" <[email protected]>
wrote in message
I'm a relative newbie at VB but I've managed to cobble together bits
of code gleaned from this NG which enables me to extract selected
data from a group of closed workbooks located in a particular folder
( located at "C:\Temp\Survey) and then save it to another workbook
called Update.xls, in worksheet called Get Data. This is based on two
macros - the most significant of which is based on a development
originally done by Nikos Yannacopoulos.

I want to write an extra couple of lines that will get the name of
each closed workbook in the folder. Here is the code for both macros.
the latter one has been shortened so that it shows the principle
involved. Here is the code:-

Private Sub Process_All_WorkBooks_Click()
' Process_All_Workbooks Macro
' This Macro will open all spreadsheets in a particular folder.
' It will then run another Macro
' Macro created by Nikos Yannacopoulos.

Dim fs, f, f1
Dim strFile As String
fldr = "C:\Temp\Survey\" 'Put your folder path here.
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(fldr)
Set f1 = f.Files
i = 0
Application.ScreenUpdating = False
For Each f2 In f1
strFile = fldr & f2.Name
If Right(strFile, 4) = ".xls" Then
Workbooks.Open strFile

Call Get_Data(f2.Name) 'Call another macro or write code to do
what youwant here.
Windows(f2.Name).Activate
ActiveWorkbook.Close savechanges:=False 'Change accordingly.
True will save the spreadsheet and False will not.
End If
Next
Application.ScreenUpdating = True
End Sub

Private Sub Get_Data(ExcelFileName As String)
Workbooks(ExcelFileName).Worksheets("Working").Range("L2:L2").Copy
Workbooks("Update.xls").Worksheets("Get Data").Range("E65536").End
(xlUp).Offset(1).PasteSpecial
End Sub

Any help would be much appreciated.
VT

 

[Ron de Bruin]

See alo this add-in
http://www.rondebruin.nl/merge.htm
It also create a log file with the file names

 

[Shane Devenshire]

Hi,

Here is one block of code to open each workbook in a specified folder and do
something to it:

Dim myFile as String
myFile = Dir("*.xls")
Do Until myFile = ""
'your code here
myFile = Dir
Loop

With this code you might store each filename in a cell or a variable, for
example
Cells(I,1) = myFile
could be added in the loop with I incrementing.

This code comes from Reed Jacobson's Step by Step book.

 

[Vet Tech]

Thanks Jim . That worked. It repeated the previous cell that was
copied but I've just hidden that so it gives me the result I wanted.

 

[Vet Tech]

See alo this add-inhttp://www.rondebruin.nl/merge.htm
It also create a log file with the file names

Ron,
That looks awsome. Many thanks.

VT