VBA script to capture file path

[Sarah333]

I need a script to capture the A file path, as my macro opens named files which reside in a different location to the master file that contains the macro, I want to avoid hardcoding the paths and end users having to change the VBA code every month for example

Master file resides in
c:\accounts\MasterFiles\

the files that the master file macro uses are located in: (This location changes every month.)

c:\accounts\monthendfiles\2012\Jan\



so I either need the macro to reference a range in the spreadsheet that the end user inputs the year and month folder names so this can be called into the path name, or a prompt and browse to the folder path that can be referenced in the macro.

woudl really appreciate any assistance
Sarah

 

[vaskov17]

Sub SelectFolder()
    Dim fd As FileDialog
    Dim sPath As String
    
    Set fd = Application.FileDialog(msoFileDialogFolderPicker)
    
    If fd.Show = -1 Then
        sPath = fd.SelectedItems(1)
    End If
    
    'sPath now holds the path to the folder or nothing if the user clicked the cancel button
    MsgBox sPath
End Sub

 

[kpark91]

Assuming you have Year in column A and month in column B. Put this code in your master file. All the data should be filled to the last row.

Sub a()
    Dim LR_A As Long, i As Long
    LR_A = Range("A" & Rows.count).End(xlUp).Row

    Dim masterFilePath As String, filePath As String
    'You can return the path of master file with this.
    masterFilePath = ThisWorkbook.Path

    'You can append the strings with '&'
    For i = 2 To LR_A
        filePath = ThisWorkbook.Path & "\" & Range("A" & i).Value & "\" Range("B" & i).Value
        'Do whatever in here.
    Next i 
End Sub

Not tested.

 

[Sarah333]

Thank you both very much I will give them a go

really appreciate your quick response
Sarah

 

[Sarah333]

vaskov17

this works well thank you but when I try to use the sPath variable to open a workbook I get an error

Workbooks.Open Filename:=sPath & "load1.xls"

what am I doing wrong?
Thanks
Sarah

 

[kpark91]

Try
Workbooks.Open FileName:=sPath & "\load1.xls"

 

[Sarah333]

kpark91
Thank you very much this works
I had tried every combination bar that!

thanks & have a good day
Sarah

 

[Sarah333]

Following on from the above, the prompt for file name worked well but I have a similar file that really needs this to be non prompt, therefore I have written the below but its not working

v_A: is the path stored in the spreadsheet to the lowest comon parent ie "C:\Accounts\Monthend\"

v_B: is the month folder name that changes every mnoth ie "05 May" this cell is a text format

can you help please?
thanks in advance
-----------------------------------------

Sub TestOpen()
Dim v_A As Object
Dim v_B As Object
Dim v_C As String
Dim MyFilePath As String


v_A = Worksheets("Sheet1").Range("C1")
v_B = Worksheets("Sheet1").Range("C2")
v_C = "\02 - Emails\Files sent"
MyFilePath = v_A & "\" & v_B & v_C


Workbooks.Open Filename:=MyFilePath & "\Test1.xls"

End Sub

note using xp and excel 2003(waiting on windows 7 !)

 

[kpark91]

So Close!

Sub TestOpen()
 Dim v_A As String
 Dim v_B As String
 Dim v_C As String
 Dim MyFilePath As String
  
 
 v_A = Worksheets("Sheet1").Range("C1").Value
 v_B = Worksheets("Sheet1").Range("C2").Value
 v_C = "\02 - Emails\Files sent"
 MyFilePath = v_A & "\" & v_B & v_C
 
 
      Workbooks.Open Filename:=MyFilePath & "\Test1.xls"
  
End Sub

 

[Sarah333]

Excellent, thank you so much, that works perfectly!