capture filename

vicalworks

New Member
Joined
Aug 11, 2005
Messages
28
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
 

Excel Facts

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

Well-known Member
Joined
Apr 28, 2004
Messages
76,286
Office Version
  1. 365
Platform
  1. Windows
Al

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

Can you post the current code?

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

New Member
Joined
Aug 11, 2005
Messages
28
Thanks to all. I got it to work with the code

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

Al :biggrin: That was ez!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top