VBA export filename and path

[jessmendez01]

Need alot of help
what's wrong with my code???? i need it to extract the file name and path it is taking data from
im still new to VBA and no one in my office seems to be able to have the time to help me

-----------------------------------------------------------

Option Explicit


Sub GetPartOfFilePath()


Dim myPath As String
Dim myOutput As String


'Retrieve ActiveWorkbook's File Path (Displayed in Immediate Window [ctrl + g])
    Dim originalFile As String
    Dim NewFile As String
    originalFile = ActiveWorkbook.name
    NewFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Please select a file", MultiSelect:=False) 'only excel files
    Workbooks.Open NewFile
    NewFile = Dir(NewFile)


'Retrieve File Name with Extension (Displayed in Immediate Window [ctrl + g])
  myOutput = Right(myPath, Len(myPath) - InStrRev(myPath, "\"))
  Debug.Print "File Name (w/ ext): " & myOutput


End Sub

[Kenneth Hobs]

Sub GetPartOfFilePath()  Dim myPath As String, myOutput As String, originalFile As String, NewFile As String
  Dim wb As Workbook
  'Retrieve ActiveWorkbook's File Path (Displayed in Immediate Window [ctrl + g])
  originalFile = ActiveWorkbook.Name
  NewFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Please select a file", MultiSelect:=False) 'only excel files
  Set wb = Workbooks.Open(NewFile)
  NewFile = Dir(NewFile)
   'Retrieve File Name with Extension (Displayed in Immediate Window [ctrl + g])
  myOutput = Right(myPath, Len(myPath) - InStrRev(myPath, "\"))
  Debug.Print "File Name (w/ ext): " & myOutput
  Debug.Print "Opened Workbook's Fullname: ", wb.FullName
End Sub

[jessmendez01]

Hey kenneth - thanks it's pull the files but it's just opening it ..what i need it to do is copy and paste in the active cell the actual file name and path that its saved at.

[Kenneth Hobs]

activecell.value = wb.fullname

[jessmendez01]

where do i put activecell.value = wb.fullname

[jessmendez01]

i have put it all over the place and it's not working to extract the file name and path to my new workbook

this is what i have ..

Sub GetPartOfFilePath()



Dim myPath As String
Dim myFile As String
Dim originalFile As String
Dim NewFile As String

'Retrieve File Name with Extension (Displayed in Immediate Window [ctrl + g])
myPath = Left$(NewFile, InStrRev(NewFile, "\") - 1)
myFile = Right$(NewFile, Len(NewFile) - InStrRev(NewFile, "\"))
Debug.Print "File Name (w/ ext): " & myPath & " - " & myFile
ActiveCell.Value = myPath.FullName


End Sub

[Kenneth Hobs]

Please paste code between code tags. To insert the tags, click the # icon.

MyPath is a string. MyFile is a string. Neither is needed. Workbook objects have properties like Fullname, Path, Name, etc. If you have the fullname, there are better ways than string parsing as you did to get parts like the file extension, basename, etc. Of course if you are happy with your string parsing method, go for it.

I would not recommend putting the value into ActiveCell. You don't know which sheet or cell will be active when you open a workbook.

You can use a cell formula to poke the Fullname value into. If you are using the fullname string in VBA, there is no need since it is available through the Workbook object at any time.

Sub GetPartOfFilePath() 
    Dim myPath As String, myOutput As String, originalFile As String, NewFile As String
     Dim wb As Workbook 
     'Retrieve ActiveWorkbook's File Path (Displayed in Immediate Window [ctrl + g])
    originalFile = ActiveWorkbook.Name 
    NewFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Please select a file", MultiSelect:=False) [COLOR=darkgreen]'only excel files
    Set wb = Workbooks.Open(NewFile) 
    NewFile = Dir(NewFile) 
     'Retrieve File Name with Extension (Displayed in Immediate Window [ctrl + g])
     'myOutput = Right(myPath, Len(myPath) - InStrRev(myPath, "\")) 
     'Debug.Print "File Name (w/ ext): " & myOutput 
     'Debug.Print "Opened Workbook's Fullname: ", wb.FullName
      'activecell.value = wb.fullname
     'wb.Worksheets(1).Range("A1").Value = wb.Fullname
     wb.Worksheets("Sheet1").Range("A1").Value = wb.Fullname

[jessmendez01]

THis is what i came up with ..it's opening the 2nd workbook that i am pulling data from however it reports back to my newfile with the activeworkbook full name which what i need it to report back is the originalfile (where data was pulled) file name and path
Sub mirroring()


Dim originalFile As String
Dim NewFile As String
originalFile = ActiveWorkbook.name
NewFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Please select a file", MultiSelect:=False) 'only excel files
Workbooks.Open NewFile
NewFile = Dir(NewFile)

Windows(NewFile).Activate
Windows(originalFile).Activate
ActiveCell = ActiveWorkbook.FullName

End Sub

[Kenneth Hobs]

I don't know what you mean by data pulling.

If you want the workbook with the macro in it it's Fullname property value then use ThisWorkbook rather than wb.

[jessmendez01]

ok i have two workbooks open lets call them original and newfile
in my new file i have a macro already completed to pull only certain data from the original file.

Now i need in newfiles activecell to pull the filename and path of the original file where im pulling the data from.

i have below: but it's only putting the file name of the new file currently opened but not the original file which is what i need.

Sub mirroring()


Dim originalFile As String
Dim NewFile As String
originalFile = ActiveWorkbook.name
NewFile = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls*), *.xls", Title:="Please select a file", MultiSelect:=False) 'only excel files
Workbooks.Open NewFile
NewFile = Dir(NewFile)

Windows(NewFile).Activate
originalFile = ActiveCell = ActiveWorkbook.FullName


End Sub