Enter File name in cell

[Kashyap]

I have a macro to open a file and I want to enter the file name from which
macro is run to a cell in opened file.

If I open abc.xls from file xyz.xls, then I want value of cell F2 in abc.xls
to be xyz

 

[Peter T]

Sub test()
Dim sFile As String
Dim ws As Worksheet
Dim wb As Workbook
sFile = "C:\Documents and Settings\Owner\My Documents\abc.xls"

On Error Resume Next
Set wb = Workbooks.Open(sFile)
On Error GoTo 0

If wb Is Nothing Then
MsgBox "File not found" & vbCr & sFile
Exit Sub
End If

Set ws = wb.Worksheets("Sheet2")
ws.Range("F2") = ThisWorkbook.Name
ws.Activate

End Sub

Regards,
Peter T

 

[OssieMac]

Workbooks.Open Filename:= _
"C:\Users\OssieMac\Documents\Excel\Test Macros\ABC.xls"

ActiveWorkbook.Sheets("Sheet1").Range("F2") = ThisWorkbook.Name