Updating Macro to use Folder path specified by user

JustRob

New Member
Joined
Jul 22, 2014
Messages
3
I altered this code and its working as is, but I'm trying to make an update to it and can't get it to work. Update runs a separate macro that allows a user to select a folder path and stores the folder path in a cell (cell L1). Instead of this piece of code having the file path written in as shown, I need it to reference Cell L1 and select that folder path. This current macro I'm trying to update goes to the folder path shown and pulls a pre-determined piece of every excel file in the folder and drops it into my new Master Sheet - basically consolidates all the data I'm looking for into 1 sheet.

Can I get help to re-write the section of this macro (bold below) that has the folder path written in, to instead use the folder path in cell L1?

Rich (BB code):
Sub Consolidate()

Dim fName As String, fPath As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wsMaster As Worksheet

'Setup
    Application.ScreenUpdating = False  'speed up macro execution
    Application.EnableEvents = False    'turn off other macros for now
    Application.DisplayAlerts = False   'turn off system messages for now
  
    Set wsMaster = ThisWorkbook.Sheets("Data Pull")    'sheet report is built into

With wsMaster
    If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
        .UsedRange.Offset(1).EntireRow.Clear
        NR = 2
    Else
        NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1    'appends data to existing data
    End If

'Path and filename (edit this section to suit)
    fPath = "Q:\QM\Sample Files\"           
    On Error GoTo 0
    fName = Dir(fPath & "*.xlsm*")        'listing of desired files, edit filter as desired

'Import a sheet from found files
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then              'don't reopen this file accidentally
            Set wbData = Workbooks.Open(fPath & fName)  'Open file

        'This is the section to customize, replace with your own action code as needed
            LR = Range("A" & Rows.Count).End(xlUp).Row  'Find last row
            Range("B11:I149" & LR).Copy .Range("A" & NR)
            wbData.Close False                                'close file
            NR = .Range("A" & .Rows.Count).End(xlUp).Row + 1  'Next row
            End If
        fName = Dir                                       'ready next filename
    Loop
End With

ErrorExit:    'Cleanup
    ActiveSheet.Columns.AutoFit
    Application.DisplayAlerts = True         'turn system alerts back on
    Application.EnableEvents = True          'turn other macros back on
    Application.ScreenUpdating = True        'refreshes the screen
End Sub
 
Last edited by a moderator:

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hi & welcome to MrExcel.
How about
VBA Code:
fpath = .Range("L1").Value
If Right(fpath, 1) <> "\" Then fpath = fpath & "\"
 
Upvote 0
Hi & welcome to MrExcel.
How about
VBA Code:
fpath = .Range("L1").Value
If Right(fpath, 1) <> "\" Then fpath = fpath & "\"
Thanks Fluff for the response. I tried using Range to call on value, but was unsuccessful. I attempted adding in your piece and received a Run-time error '52', Bad file name or number. It seems it gets passed the fpath = .Range("L1").Value piece, but gets hung up on the next line: fName = Dir(fpath & "*.xlsm*"). The path in L1 looks the same as in the working Macro that already has the path written in, so the folder path shouldn't be the issue. Any other suggestions?
 
Upvote 0
If you add this before the Dir line what does it say
VBA Code:
MsgBox fpath
 
Upvote 0
If you add this before the Dir line what does it say
VBA Code:
MsgBox fpath
You're on point Fluff. Extra character was being drawn in, corrected and your 2 lines that you suggested worked like a charm. Beautiful! Thanks a million!!!
 
Upvote 0
Glad it's sorted & thanks for the feedback.
 
Upvote 0

Forum statistics

Threads
1,213,567
Messages
6,114,342
Members
448,570
Latest member
rik81h

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top