allenayres83
New Member
- Joined
- Jan 19, 2021
- Messages
- 4
- Office Version
- 2016
- Platform
- Windows
I have a macro set up to merge excel worksheets into into a single workbook from a list of file names. The macro works but I get a popup at the end saying that it cant find "mydirectory/". What I beleive is happening is that excel is looking past my last entry into an empty cell causing the macro to look for a file with no name. Below is my code. Is there a way to fix this?
Also, I can get this code to pull files from a local drive but not my network drive. I have to log in to a computer to use the network drive for work. So I am guessing that it cant access it because it is password protected. Could that be the problem and if so can that be fixed?
Also, I can get this code to pull files from a local drive but not my network drive. I have to log in to a computer to use the network drive for work. So I am guessing that it cant access it because it is password protected. Could that be the problem and if so can that be fixed?
VBA Code:
Option Explicit
Enum ReadColumns
rcItemCode = 3
rcLineItem = rcItemCode - 1
rcSupplementalDescription = rcItemCode + 2
rcUnits = rcItemCode + 1
rcBidQuantity = rcItemCode + 5
rcUnitPrice = rcItemCode + 4
End Enum
Sub MergeandFillHeader()
Dim v As Variant, i As Long
Dim wb As Workbook, n As Long
With Application
.ScreenUpdating = False
.Calculation = xlAutomatic
End With
With ThisWorkbook
v = .Worksheets("Data").Range("C11").CurrentRegion.Value
For i = 3 To UBound(v)
Set wb = Workbooks.Open("C:\Users\d04m9\Desktop\test" & "\" & v(i, rcItemCode) & ".xlsm")
wb.Sheets(1).Copy After:=.Sheets(.Sheets.Count)
With .Sheets(.Sheets.Count)
.Cells(1, 4).Value = Worksheets("Merge").Range("D1").Value
.Cells(3, 4).Value = v(i, rcLineItem)
.Cells(4, 4).Value = v(i, rcSupplementalDescription)
.Cells(6, 4).Value = v(i, rcBidQuantity)
.Cells(8, 4).Value = v(i, rcUnitPrice)
End With
n = n + 1
wb.Close False
Next i
End With
With Application
.ScreenUpdating = True
.Calculation = xlCalculationAutomatic
End With
MsgBox "Processed " & n & " files", Title:="Merge Excel files"
End Sub