Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to log .xls file names
I have an existing macro that first allows the user to open an xls
file, then it copies data from the selected file into the one where the macro is located. This info is added to the existing data that's already there. The user will run the macro multiple times (once for each file to be added) ....thus ending with a "master" file composed of data from each file they had selected. That part is done. Now, on a separate sheet in the macro file I'd like to create a log where each row shows the name of each file that had been opened and copied into the master, along with the date when this was done. How would such a macro work? Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to log .xls file names
I'd create a log worksheet and give it a worksheet code name that won't
change (Say "Logsheet") Let's assume when you open the worksheet, you do it something like this Dim oWB as workbook set oWB = workbooks.open(filename) I'm also going to assume that you reference the active book (the book that you are writing data to) as aWB (with the appropriate dimensions) Dim oWB As Workbook Dim aWB As Workbook Dim WS As Worksheet Dim myWS As Worksheet Dim lRow As Long Set aWB = ActiveWorkbook For Each WS In aWB.Worksheets If WS.CodeName = "Logsheet" Then Set myWS = WS Exit For End If Next WS If Not myWS Is Nothing Then lRow = myWS.Cells(myWS.Rows.Count, 1).End(xlUp).Row If lRow < myWS.Rows.Count Then myWS.Cells(lRow + 1, 1) = oWB.Name myWS.Cells(lRow + 1, 2) = Date End If End If -- HTH, Barb Reinhardt " wrote: I have an existing macro that first allows the user to open an xls file, then it copies data from the selected file into the one where the macro is located. This info is added to the existing data that's already there. The user will run the macro multiple times (once for each file to be added) ....thus ending with a "master" file composed of data from each file they had selected. That part is done. Now, on a separate sheet in the macro file I'd like to create a log where each row shows the name of each file that had been opened and copied into the master, along with the date when this was done. How would such a macro work? Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Macro recorded... tabs & file names changed, macro hangs | Excel Worksheet Functions | |||
File Names within a Macro Function | Excel Worksheet Functions | |||
Macro File Names | Excel Worksheet Functions | |||
Macro File Names | Excel Worksheet Functions | |||
Can a Macro use Different File Names? | Excel Programming |