Copy File Name Into Sheet

[ai1094]

Hi I have part of my macro pasted below. The problem I'm having is I'm getting the full path name of the file, which is not what I want. How can i get ONLY the file name itself pasted into cell A1?

Set Wbk1 = ActiveWorkbook
Fname = Application.GetOpenFilename
Set Wbk2 = Workbooks.Open(Fname)
Wbk2.Sheets(1).Copy , Wbk1.Sheets(Wbk1.Sheets.Count)
Range("A1") = Fname
Wbk2.Close False
Wbk1.Sheets(Wbk1.Sheets.Count).Name = "REF"

 

[Joe4]

Try:

[COLOR=#333333]Range("A1") = [/COLOR]ActiveWorkbook.Name

 

[ai1094]

In my macro, I already have a workbook opened. What I am doing is opening another workbook and copying the data from that workbook into the current. So I need the current workbook to have the other workbook's file name in cell A1. Does that make sense?

Try:

[COLOR=#333333]Range("A1") = [/COLOR]ActiveWorkbook.Name

 

[Fluff]

How about

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

 

[ai1094]

Didn't work. It copied the workbook's file name that is already opened. I need Workbook2's file name inside A1 in Workbook1. Workbook2 is the file that gets selected when the prompt comes out.

How about

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

 

[Fluff]

In that case change Wbk1 to Wbk2

 

[ai1094]

Perfect. Thank you.

 

[Fluff]

Glad we could help & thanks for the feedback

 

[Joe4]

In my macro, I already have a workbook opened. What I am doing is opening another workbook and copying the data from that workbook into the current. So I need the current workbook to have the other workbook's file name in cell A1. Does that make sense?

When you open Wkbk2, that is then the ActiveWorkbook (until you close it or move off of it). So if you place it where you had you previous line, it should work.
Still, it is safer to follow Fluff's advice, and use the explicit workbook reference from whose name you wish to retrieve.

I am not 100% clear which workbook you want to place the value in, so you may need to account for that too, if you want to put it in Wkbk1.