Explore our sites

Stack Exchange
tour help careers 2.0
Take the 2-minute tour ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite

I have written an Excel VBA macro that compiles all the information from various spreadsheets that are located in a specific folder and compiles them into one 'Master' Excel workbook.

This currently works fine when using it on my computer, but I would like to adjust the code so that I can place the 'Master' spreadsheet and the folder containing the individual spreadsheet (the ones to be compiled) on a network drive, so that anyone can use it.

I am fairly new to VBA and coding in general so I have a strong feeling there is probably an easy solution to fix my issue.

I have attached my current macro that runs the absolute reference.

'Summary:    Open all Excel files in a specific folder and merge data
'            into one master sheet (stacked)

Dim fName As String, fPath As String, fPathDone As String, OldDir As String
Dim LR As Long, NR As Long
Dim wbData As Workbook, wbkNew As Workbook

'Setup
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.DisplayAlerts = False

Set wbkNew = ThisWorkbook
wbkNew.Activate
Sheets("Master").Activate

If MsgBox("Import new data to this report?", vbYesNo) = vbNo Then Exit Sub

If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
    Cells.Clear
    NR = 1
Else
    NR = Range("A" & Rows.Count).End(xlUp).Row + 1
End If


fPath = "C:\Folder-that-Excel-workbooks-are-located-in"
On Error Resume Next
    MkDir fPathDone
On Error GoTo 0
OldDir = CurDir
ChDir fPath
fName = Dir("*.xlsx")


Do While Len(fName) > 0
    If fName <> wbkNew.Name Then

        Set wbData = Workbooks.Open(fName)



        LR = Range("C" & Rows.Count).End(xlUp).Row
        If NR = 1 Then
            Range("C5:F" & LR).EntireRow.Copy _
                wbkNew.Sheets("Master").Range("A" & NR)
        Else
            Range("C5:F" & LR).EntireRow.Copy _
                wbkNew.Sheets("Master").Range("A" & NR)
        End If

        wbData.Close False

        NR = Range("C" & Rows.Count).End(xlUp).Row + 1

        fName = Dir
    End If
Loop

ErrorExit:
ActiveSheet.Columns.AutoFit
Application.DisplayAlerts = True
Application.EnableEvents = True
Application.ScreenUpdating = True
ChDir OldDir
share|improve this question
add comment

1 Answer

up vote 0 down vote

One quick and dirty solution would be putting the path to the workbook folder somewhere into the master workbook.

Put the other workbooks on a network share that is available to all computers you are sharing your excel sheet with. Use a UNC path like this:

\\ComputerName\SharedFolder\Resource

You can then set fPath in your code to the cells value.

A better way would be putting the path into a settings file in the same folder as the master workbook and reading the path when running the macro:

Dim tmpArray() As String
Dim s As String
Dim strPath as String
Open ThisWorkbook.Path & "\settings.ini" For Input As #1
    Do While Not EOF(1)
        Line Input #1, s
        If VBA.Left(s, 11) = "excelfolder" Then
            tmpArray = Split(s, "=")
            strPath = tmpArray(1)
        End If
    Loop
Close #1

Your ini file would look like this:

excelfolder=\\ComputerName\SharedFolder\Resource
share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.