+ Reply to Thread
Results 1 to 11 of 11

DIM a folder path

Hybrid View

Crispy85 DIM a folder path 08-10-2015, 08:46 AM
Norie Re: DIM a folder path 08-10-2015, 08:50 AM
Crispy85 Re: DIM a folder path 08-10-2015, 08:58 AM
Norie Re: DIM a folder path 08-10-2015, 08:59 AM
kadeo Re: DIM a folder path 08-10-2015, 09:04 AM
:) Sixthsense :) Re: DIM a folder path 08-10-2015, 09:05 AM
Crispy85 Re: DIM a folder path 08-10-2015, 09:23 AM
kadeo Re: DIM a folder path 08-10-2015, 09:28 AM
:) Sixthsense :) Re: DIM a folder path 08-10-2015, 09:35 AM
Norie Re: DIM a folder path 08-10-2015, 09:30 AM
Crispy85 Re: DIM a folder path 08-10-2015, 10:04 AM
  1. #1
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    DIM a folder path

    Hi,

    I have this little script to open a .csv file.
    However, i would like to 'lock' the folder path without the filename too, so that i can call it again in later steps of the macro, to save the files in the same dir.

     Dim fStr As String
    
     With Application.FileDialog(msoFileDialogFilePicker)
     .Show
     If .SelectedItems.Count = 0 Then
     MsgBox "Cancel Selected"
     Sheets("Dashboard").Select
     Exit Sub
     End If
     'fStr is the file path and name of the file you selected.
     fStr = .SelectedItems(1)
     End With
    fStr is set as the selected file.
    What should i insert to save the folder path?

    Thanks for all the help

  2. #2
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: DIM a folder path

    fStr actually contains the file name and path.

    If you want to extract only the path try this.
    fPath = Replace(fStr, Dir(fStr), "")
    If posting code please use code tags, see here.

  3. #3
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: DIM a folder path

    Hi Norie,

    Thank you. I want to try this but it doens't really work. I think because of this part:
    Dim sh As Worksheet
    For Each sh In ThisWorkbook.Sheets
        sh.Copy
        Application.ActiveWorkbook.SaveAs Filename:=fPath & "\" & sh.Name & ".xls"
        Application.ActiveWorkbook.Close False
    Next
    i get a error at line "sh.copy"

    Do you know what goes wrong?

  4. #4
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: DIM a folder path

    I really don't see how sh.Copy is connected with the path of the file.

    What error are you getting on that line?

  5. #5
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: DIM a folder path

    You can get the path from the whole filename and path using InStrRev

    EDIT: Sorry, the original one was for the filename, the path is as follows:

    fPath = Left(fStr, InStrRev(fStr, "\") - 1)
    Last edited by kadeo; 08-10-2015 at 09:09 AM.
    Please click *Add Reputation if I've helped

  6. #6
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,789

    Re: DIM a folder path

    May be replace the

    ThisWorkbook.Sheets
    With

    ThisWorkbook.Worksheets
    since sh defied as Worksheet


    If your problem is solved, then please mark the thread as SOLVED>>Above your first post>>Thread Tools>>
    Mark your thread as Solved


    If the suggestion helps you, then Click *below to Add Reputation

  7. #7
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: DIM a folder path

    Thanks all.
    I looked up another script on the net but this also results in an error...
    Dim WS As Worksheet
    
        For Each WS In ThisWorkbook.Worksheets
            WS.SaveAs fPath & WS.Name, xls
        Next
    Method 'SaveAs' of object '_Worksheet' failed.

  8. #8
    Forum Contributor
    Join Date
    05-27-2014
    Location
    Belfast, Northern Ireland
    MS-Off Ver
    2007 & 2010
    Posts
    273

    Re: DIM a folder path

    You can't save a worksheet, only a workbook.

    If you want to save a worksheet, you need to copy it to a new workbook first then save that workbook.

  9. #9
    Forum Guru :) Sixthsense :)'s Avatar
    Join Date
    01-01-2012
    Location
    India>Tamilnadu>Chennai
    MS-Off Ver
    2003 To 2010
    Posts
    12,789

    Re: DIM a folder path

    WS.SaveAs fPath & WS.Name, xls
    I think you have to include the file extension along with the file name. Have a look in the macro recorder and see how it saves the file

  10. #10
    Forum Guru Norie's Avatar
    Join Date
    02-02-2005
    Location
    Stirling, Scotland
    MS-Off Ver
    Microsoft Office 365
    Posts
    19,646

    Re: DIM a folder path

    What are you actually trying to do?

  11. #11
    Forum Contributor
    Join Date
    06-14-2013
    Location
    jk
    MS-Off Ver
    Excel 2007
    Posts
    326

    Re: DIM a folder path

    Thanks for all the replies guys, it worked at the end with help of the tips to extract the path.

    The code i used is:

    Dim xWs As Worksheet
    For Each xWs In ActiveWorkbook.Sheets
        xWs.Copy
        Application.ActiveWorkbook.SaveAs Filename:=fPath & "\" & xWs.Name & ".xls"
        Application.ActiveWorkbook.Close False
    Next

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. How to validate an input dat folder/directory path whether the path has any spaces
    By mahendra.asapu in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-21-2015, 02:39 AM
  2. Choosing folder path (instead of file path) in VBA
    By sminter in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 12-23-2014, 08:50 PM
  3. Open Folder (Prompt User for final folder name in location path)
    By synergeticink in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 11-21-2014, 03:33 PM
  4. [SOLVED] Copy files from one folder to another folder (path in textbox)
    By yoko21 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 06-24-2014, 02:45 AM
  5. Code to list the folder path and sub folder path of a specific file
    By kalai1587 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-13-2013, 03:51 AM
  6. Specified folder path to select file path
    By JayEmTee91 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 10-29-2012, 10:38 AM
  7. List folder path according to folder index
    By naflas in forum Excel General
    Replies: 4
    Last Post: 07-15-2010, 07:33 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1