Consulting

Results 1 to 5 of 5

Thread: VBA extracting file name and path HELP ::::

  1. #1

    VBA extracting file name and path HELP ::::

    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

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

    Sub GetPartOfFilePath()
    Dim myPath As String
    Dim myOutput As String
    Dim originalFile As String
    Dim NewFile As String
    
    '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
    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
    End Sub
    Last edited by Bob Phillips; 05-03-2016 at 04:02 AM. Reason: Added code tags

  2. #2
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Sub GetPartOfFilePath()
    Dim myPath As String
    Dim myFile As String
    Dim originalFile As String
    Dim NewFile As String
    
        '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
        Workbooks.Open NewFile
        
        '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
    End Sub
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  3. #3
    it's not working for me it will open the path and open the workbook but will not copy into my new workbook the file name and path

  4. #4
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    There is nothing in that code copy anything, so it is hardly surprising.
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

  5. #5

    VBA MACROS - filename issue

    this is my new macro ..it opens that old file but doens't copy the filename and path to paste in my active cell in my new file
    any help?
    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
         'activecell.value = wb.fullname
         'wb.Worksheets(1).activecell.Value = wb.Fullname
    
    End Sub
    Last edited by Bob Phillips; 05-04-2016 at 03:41 AM. Reason: Added code tags

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •