capture filename

[vicalworks]

Hi eveyrone. im trying to capture the filename of the workbook that i opened using a macro.

So lets say Workbook 1 has the macro.
Macro looks in the folder, opens all the workbooks.
Select worksheet (Sheet1).
Selects cell range D1.
Copy the data from each worksheet.
Select Workbook 1.
Paste the data to first available row in column B.
Now in column A, I want to print the name of the file that
relates to column B data.

I have the code to open the files. My problem is how do I capture the
filename and display it on Column A relating to Column B data???

Thanks - Al

 

[Andrew Poulsom]

When you open the workbook assign it to an object variable, eg:

Dim wb As Workbook
Set wb = Workbooks.Open("TheWorkbook")

Then you can get its name like this:

Range("A1").Value = wb.Name

 

[Norie]

Al

It really depends on how you are opening the file(s).

Can you post the current code?

Dim wbOpen As Workbook

' open and create a reference to Myfile.xls
Set wbOpen = Workbooks.Open("C:\Myfile.xls")

' we can now refer to the newly opened workbook in code using wbOpen
Msgbox "You've just opened " & wbOpen.Name

 

[vicalworks]

Thanks to all. I got it to work with the code

Range("A1").Value = wb.Name

Al That was ez!