Hello,
I am trying to prep a batch of files in a folder for use in a database, so my code (pasted in whole below) attempts to 1) create a number of columns with ID codes for each row, 2) clean up the existing files' field headings, 3) delete unnecessary rows, and 4) compile them all into one worksheet.
I'm a novice to VBA and programming in general, so my main method of developing programs is mostly through copying-and-pasting from google (lmao), which absolutely explains the errors.
The first error (of many to come, I'm sure) is an "end with" error. Any pointers on how to solve that?
Additionally, could someone take the time to point out any glaring inconsistencies or inefficiencies in this code? I know that might be a bit of a tall order, but I would really appreciate the criticism!
Thanks!
Here's the code:
Code:Sub combiningFiles() 'IDing all files Dim wb As Workbook Dim strFile As String, strDir As String Dim headers() As Variant headers() = Array("Data Number", "Date-Time", "Temperature", "Relative Humidity", "Concentration") strDir = "C:\Users\..." strFile = Dir(strDir & "*.xlsx") Do While strRile <> "" Set wb = Workbooks.Open(Filename:=strDir & strFile, Local:=True) With wb 'Inserting a column at column i Range("I1").EntireColumn.Insert 'Filling new column with pollutant ID Dim LastRow As Long LastRow1 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("I3:I" & LastRow).Value = "CO2" 'Inserting a column at column j Range("J1").EntireColumn.Insert 'Filling new column with location ID LastRow2 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("J3:J" & LastRow).Value = "E" 'Inserting a column at column j Range("K1").EntireColumn.Insert 'Filling new column with room ID LastRow3 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("K3:K" & LastRow).Value = "Living Room" 'Inserting a column at column l Range("L1").EntireColumn.Insert 'Filling new column with home ID LastRow4 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("L3:L" & LastRow).Value = "Living Room" 'Inserting a column at column m Range("M1").EntireColumn.Insert 'Filling new column with home ID LastRow5 = Cells.Find(What:="*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row Range("M3:M" & LastRow).Value = Mid(FilePath, InStrRev(FilePath, "") + 12, InStrRev(FilePath, ".") - InStrRev(FilePath, "") - 12) 'Simplify field headings .Rows(2).Value = "" 'This will clear out row 2 For i = LBound(headers()) To UBound(headers()) .Cells(2, 2 + i).Value = headers(i) 'Delete first row Rows(1).Delete 'Compile all files in a folder into one spreadsheet Workbooks.Open Filename:=Path & Filename, ReadOnly:=True For Each Sheet In ActiveWorkbook.Sheets Sheet.Copy After:=ThisWorkbook.Sheets(1) Next Sheet Workbooks(Filename).Close Filename = Dir() Set wb = Nothing strFile = Dir End With Loop End Sub
Last edited by RoryA; Dec 7th, 2017 at 09:25 AM. Reason: Code tags
Welcome to the Board!
Sometimes those errors messages are a bit misleading. All loops and With statements need to be closed. You are actually missing the closing "Next" statement for your "For i..." loop (you have two "For" statements, but only one "Next" statement).
Note that this things are much easier for us to see if you use Code tags so the indenting and spacing on your code isn't lost when you paste it here.
See: https://www.mrexcel.com/forum/board-...-vba-code.html
TIPS FOR FINDING EXCEL SOLUTIONS
1. Use the built-in Help that comes with Excel/Access
2. Use the Search functionality on this board
3. A lot of VBA code can be acquired by using the Macro Recorder.
"Give a man a fish, feed him for a day. Teach a man to fish, feed him for life!"
Like this thread? Share it with others