Updating a master sheet from nightly data

I'm having a hell of a time with this one. I've managed to automate the file handling portions of this new job for creating a new daily report for my group, but the most annoying step is updating the master files with the new dumps from the FTP.

The unique ID for each file that needs to be compared is in Column 2 (B), I need to walk through each sheets column B and if there IS a match, update columns O-W with the values in the new file, and if the unique ID doesn't exist in the old file to add the entire row at the bottom of the used range of the main file.
This is an amalgam of code i've scrounged/copied and some things I wrote myself, so it's a hot mess I know.
Here's the run down.
1)I receive 4 files daily via FTP, 1 for 4 different states
2)These files are auto-archived and then the newest version of each is renamed to the two letter state name (i.e. IL.csv) and moved to a fresh data folder.
3)I have an intermediate file in a shared network drive that needs to allow for manual data entry from each state daily on actual performance that I need to auto-merge any updated values or new rows of data into from the files in step 2
4)I already have a query and cleanup run that merges the 4 intermediate files from step 4 into the master workbook that I can auto-generate my reports from.
The part that's killing me is figuring out how to read from the daily files, evaluate against the intermediate files and update certain columns if changed and add any new rows (the previous day's activity).
I definitely know that my select copy isn't accurate or working below, but I can't even get the upper portion to open and select from the files to work correctly, I keep getting "object doesn't support this property or method" on OldRange and NewRange.
Sub UpdateDataWithNew()

Dim ILmain As Workbook
Dim ILnew As Workbook
Dim data As Worksheet
Dim maintable As Worksheet
Dim OldRange AsInteger
Dim NewRange AsInteger
Dim ListOld As Range
Dim ListNew As Range
Dim x As Range
Dim y As Range


Workbooks.Open Filename:="C:\Users\adeno\Desktop\Test Load
Files\Illinois.xlsm"
Workbooks.Open Filename:="C:\Users\adeno\Desktop\Today's FTP\IL.csv"
Set ILmain = Workbooks("Illinois.xlsm")
Set ILnew = Workbooks("IL.csv")
OldRange = ILmain.Range("A2",
Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count
NewRange = ILnew.Range("A2",
Worksheets("Sheet1").Range("A2").End(xlDown)).Rows.Count

Set ListOld = Range("B2:B"& OldRange)
Set ListNew = Range("B2:B"& NewRange)

Set data = ILnew.Sheets("Sheet1")
Set maintable = ILmain.Sheets("Sheet1")


ForEach x In ListOld
Workbook.ILnew.Activate
If Application.CountIf(ListB, x)=0Then
Row.Copy
Workbook.ILmain.Activate
Row.End(x1Up).Select

Hello dbc23,

I would be more then happy to help you with your issue. If you could kindly do me a favor and upload a sample workbook (the one you are pulling data from, and the one its going too) I only need one to see what you are talking about.

Just from perusing your code, you need a space in your 'Dim' statements for 'OldRange' and 'NewRange' and, also - you need to specify a sheet within your workbooks, .Range is a method refering to a sheet, not a workbook - I.E. ILmain.Sheets(1).Range..... OR ILmain.Worksheets("Sheet1").Range.....

Also, if you could be more concise as to what you require, that would help.

From what I gather, you need to compare columns from ILnew and ILmain, and if anything from column 'B' of the new file isnt in column 'B' of the main file, paste everything from column 'B' FROM new TO main, correct?

I can really provide solutions and examples once there are examples (at least smaller dummy versions) of what you require.

Looking forward to hearing back!

Also, using the #CODE tags found in the reply/post toolbar would help organize your post more. Just select your code blocks and click the hashtag.

Much appreciated. I've been hacking at it so I've made some tweaks but still having a hard time getting the defined paths to read correctly either as a file link or a range.

There's links to examples of the two files below. The csv is the daily and the xlsm is the ongoing workbook, so I want to build the update macro into that so I can set it up to run with a batch file daily.

What I need to do is evaluate Column B for matching DRV#s (this is the unique ID), then IF there is a match ONLY update the values in columns O through W from the csv to the xlsm.
If the DRV# doesn't exist then I need to append that entire row to the bottom of the table in the xlsm file.

Files:
https://drive.google.com/open?id=1Oq...GULCdmpoNws6zb
https://drive.google.com/open?id=1gG...2rewV9UPfSFNLx

This is what I have for code at the moment:

Code:

---

Sub UpdateDataWithNew()


Dim ILmain As Workbook
Dim ILnew As Workbook
Dim OldRange As Integer
Dim NewRange As Integer
Dim ListOld As Range
Dim ListNew As Range
Dim O As Range
Dim N As Range




Set ILmain = Workbooks.Open(Filename:="C:\Users\adeno\Desktop\Test Load Files\Illinois.xlsm")
Set ILnew = Workbooks.Open(Filename:="C:\Users\adeno\Desktop\Today's FTP\IL.csv")
'ILmain = Workbooks("Illinois.xlsm")
'ILnew = Workbooks("IL.csv")
'OldRange = ILmain.Range("A2", Worksheets("Sheet1").End(xlDown)).Rows.Count
'NewRange = ILnew.Range("A2", Worksheets("Sheet1").End(xlDown)).Rows.Count


Set ListOld = ILmain.Sheets(1)
Set ListNew = ILnew.Sheets(1)


O = ListOld.Columns("B")
N = ListNew.Columns("B")


   
For Each cell In ListOld
    If cell.Value = ListNew.Columns(, 2) Then
    Row.Copy
    Workbook.ILmain.Activate
    Row.PasteSpecial
    End If
Next cell


End Sub

---

dbc23, please educate people you are requesting help from that you are cross posting.

Anyway, I put this together and tested it and it should work. There are definately more eloquent ways of doing this, but it should be fool-proof. I also did you a favor and made it universal, so you dont need multiple macros for each state (it should work regardless of the state - so you can copy paste to your other masters).

Let me know if you run into any issues!

NOTE: I also added a functionality which should prompt you to pick the .csv file, allowing it to be universal and removing any issues you had with workbook variable assignment. You need microsoft scripting runtime for this functionality. Reference this:

• To reference this file, load the Visual Basic Editor (ALT+F11)
• Select Tools > References from the drop-down menu
• A listbox of available references will be displayed
• Tick the check-box next to 'Microsoft Scripting Runtime'

Code:

---

Sub UpdateDataWithNew()
Dim mainWorkbook As Workbook
Dim newWorkbook As Workbook
Dim mainWorkbookDataSheet As Worksheet
Dim csvWorkbookDataSheet As Worksheet
Dim OldRange As Integer
Dim NewRange As Integer
Dim ListOld(), ListNew(), csvMovingData(), xlsmMovingData()
Dim xlFile As Variant
Dim fso As New FileSystemObject


' Set workbook/worksheet variables
Set mainWorkbook = ThisWorkbook
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)


xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select .csv File for Evaluation", "Open", False)
xlFileName = fso.GetFileName(xlFile) ' Gets filename of chosen file
Workbooks.Open xlFile ' Opens .mtwData Workbook


' Set workbook/worksheet variables
Set newWorkbook = Workbooks(xlFileName)
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)




xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 1 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


With mainWorkbookDataSheet
    ListOld = Range(.Cells(xlsmFirstRow, 2), .Cells(xlsmLastRow, 2)).Value
End With
With csvWorkbookDataSheet
    ListNew = Range(.Cells(csvFirstRow, 2), .Cells(csvLastRow, 2)).Value
End With


Index = 0
Counter = 1
Dim x As Range, rowIdx As Long
For i = LBound(ListNew) To UBound(ListNew)
    Found = False
    For j = LBound(ListOld) To UBound(ListOld)
        If ListNew(i, 1) = ListOld(j, 1) Then
            Set x = mainWorkbookDataSheet.Cells.Find(What:=ListNew(i, 1)) ' Finds row in xlsm workbook for the value matched from csv workbook
            rowIdx = x.Row ' Gets row index value
            mainWorkbookDataSheet.Range("O" & rowIdx & ":W" & rowIdx).Value = csvWorkbookDataSheet.Range(Cells(i, 15), Cells(i, 23)).Value ' Sets xlsm row values (Column O:W) to csv values
            Found = True
        End If
    Next j
    If Found = False Then
        mainWorkbookDataSheet.Range("A" & xlsmLastRow + Counter & ":W" & xlsmLastRow + Counter).Value = csvWorkbookDataSheet.Range(Cells(i, 1), Cells(i, 23)).Value
        Counter = Counter + 1
    End If
Next i
newWorkbook.Close
End Sub

---

AMAZING, I'll put this through it's paces but it seems to be what I needed.

Not sure what you meant by cross-posting, I did put this on stack overflow first, but then thought this forum was more appropriate, so if that's it I will keep that in mind for the future.

I love reading through the solution too, because the more I learn the more I can solve these things on my own :)

Thanks Again! and I'll give some feedback once I get it implemented.

I'm getting "Object Variable or With Variable not set" for the line:

Code:

---

rowIdx = x.Row ' Gets row index value

---

My pleasure!

Generally notifying people when you post in another forum is recommended, but I see how you only continued in this thread.

I feel the same way, I feel its the best way to learn!

And please, dont hesitate at all with any questions.

Thats likely because its not finding the matching value in the .xlsm workbook. In the sample you provided me I did not run into this error, is there a specific case this is occuring with?

If you could attach the workbook with which this happened I could diagnose the issue and get back to you! Also, if you want to attach other state's workbooks, I could troubleshoot on those and check the universal usage.

I tried adjusting the column/row settings and inverting UBound/LBound in the lookup ranges but still couldn't get it to work. Here's a copy of my test workbook, and a full import csv file example. The only thing I've modified is including the header row in the csv, which I accounted for in the macro.

https://drive.google.com/open?id=1Ey...lqOGXzwmtrfUtd
https://drive.google.com/open?id=1G6...tL1x5qc7Fn6DiV

dbc23, it seems the error was caused by the columns being hidden. I modified the code to work around this so you don't have to unhide them:

NOTE: This solution overwrites the data starting at row 3248 (where 'DriveLengthInHours' column has random data in it).

If this is what you want, then we are good - if not I can EASILY modify the code for you, just let me know, I wasn't sure!

Code:

---

Sub UpdateDataWithNew()
Dim mainWorkbook As Workbook
Dim newWorkbook As Workbook
Dim mainWorkbookDataSheet As Worksheet
Dim csvWorkbookDataSheet As Worksheet
Dim OldRange As Integer
Dim NewRange As Integer
Dim ListOld(), ListNew(), csvMovingData(), xlsmMovingData()
Dim xlFile As Variant
Dim fso As New FileSystemObject

' Set workbook/worksheet variables
Set mainWorkbook = ThisWorkbook
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)

xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
"*.xls*", 1, "Select .csv File for Evaluation", "Open", False)
xlFileName = fso.GetFileName(xlFile) ' Gets filename of chosen file
Workbooks.Open xlFile ' Opens .mtwData Workbook

' Set workbook/worksheet variables
Set newWorkbook = Workbooks(xlFileName)
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)

xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 2 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

With mainWorkbookDataSheet
    ListOld = Range(.Cells(xlsmFirstRow, 2), .Cells(xlsmLastRow, 2)).Value
End With
With csvWorkbookDataSheet
    ListNew = Range(.Cells(csvFirstRow, 2), .Cells(csvLastRow, 2)).Value
End With

Index = 0
Counter = 1
For i = LBound(ListNew) To UBound(ListNew)
    Found = False
    For j = LBound(ListOld) To UBound(ListOld)
        If ListNew(i, 1) = ListOld(j, 1) Then
            mainWorkbookDataSheet.Range("O" & j + 1 & ":W" & j + 1).Value = csvWorkbookDataSheet.Range("O" & i + 1 & ":W" & i + 1).Value ' Sets xlsm row values (Column O:W) to csv values
            Found = True
        End If
    Next j
    If Found = False Then
        mainWorkbookDataSheet.Range("A" & xlsmLastRow + Counter & ":W" & xlsmLastRow + Counter).Value = csvWorkbookDataSheet.Range(Cells(i + 1, 1), Cells(i + 1, 23)).Value
        Counter = Counter + 1
    End If
Next i
newWorkbook.Close
End Sub

---

Continue to ask any questions:)

BRILLIANT, it works!

Overwriting Drive Length in Hours is fine, realistically only "Planned Staff" and left of that needs to be preserved because I need someone to manually update that value and don't want to force them to keep a separate file to keep copying in.

I think this is going to be the solution, I just need to hard-link the newworkbook to a static file name/location so I can schedule it to run, but I'm pretty sure I can handle that bit, if not, I'll ask ;)

You've probably just saved me hundreds of hours of my life over the next year or two, thank you!

No problem at all!

Side note, make sure you copied the entirety of the last code I posted, the "i + 1's" and "j + 1's" are super important in the 'i' for-loop (they were incorrect earlier, considering the addition of the header in the .csv)

Also, what I meant by overwriting was a few rows at the bottom of the workbook, if you look at the workbook you supplied me with (the .xlsm) at the veryyyyy bottom, starting at row 3248 BUT starting at the Drive Length in Hours column (I.E. its overwriting the few rows of data you have starting at row 3248 because the last row check is performed on column "A" - this can be changed easily, though if you need that data that has nothing in columns A-right before Drive Length starting at row 3248)

So, its not overwriting anything other than these miscelaneous rows at the bottom, so all of your columns should be preserved.

Just wanna make sure its not removing data you need!

See picture attached for what I'm explaining horribly:
Attachment 22541

The garbage in those columns was just a result of a lazy delete on my part when I was testing, in the real world they'll be blank or have data to the left.

I am having an issue though with something I thought would be simple though. Rather than requesting the load file name via a popup I wanted to just link the two files in their parent directories into the macro so I could automate the macro to run unattended.

I do wonder if there's some issue that one file is on LOCAL (C:) and another is on a mapped network drive (Y:) this is setup intentionally because after these daily updates people in different regions need to go into their state files to manually enter new information for each entry.

I've gotten to this, which no longer produces any error codes, BUT it doesn't actually perform the search and merge (note: I'm working from a copy of the original so I changed the filename):

Code:

---

Workbooks.Open ("C:\Users\adeno\Desktop\Today's FTP\WI.csv")
x2FileName = fso.GetFileName("Copy of Wisconsin.xlsm") ' Gets filename of chosen file
xlFileName = fso.GetFileName("C:\Users\adeno\Desktop\Today's FTP\WI.csv") ' Gets filename of chosen file
'Workbooks.Open xlFileName ' Opens .mtwData Workbook
'Workbooks.Open x2FileName


' Set workbook/worksheet variables
Set mainWorkbook = ActiveWorkbook
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)
Set newWorkbook = Workbooks(xlFileName)
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)


xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 2 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row




With mainWorkbookDataSheet
    ListOld = Range(.Cells(xlsmFirstRow, 2), .Cells(xlsmLastRow, 2)).Value
End With
With csvWorkbookDataSheet
    ListNew = Range(.Cells(csvFirstRow, 2), .Cells(csvLastRow, 2)).Value
End With




Index = 0
Counter = 1
Dim x As Range, rowIdx As Long
For i = LBound(ListNew) To UBound(ListNew)
    Found = False
    For j = LBound(ListOld) To UBound(ListOld)
        If ListNew(i, 1) = ListOld(j, 1) Then
            Set x = mainWorkbookDataSheet.Cells.Find(What:=ListNew(i, 1)) ' Finds row in xlsm workbook for the value matched from csv workbook
            rowIdx = x.Row - 1 ' Gets row index value
            mainWorkbookDataSheet.Range("L" & rowIdx & ":W" & rowIdx).Value = csvWorkbookDataSheet.Range(Cells(i, 12), Cells(i, 23)).Value ' Sets xlsm row values (Column O:W) to csv values
            mainWorkbookDataSheet.Range("C" & rowIdx & ":H" & rowIdx).Value = csvWorkbookDataSheet.Range(Cells(i, 3), Cells(i, 8)).Value ' Sets xlsm row values (Column O:W) to csv values
            Found = True
        End If
    Next j
    If Found = False Then
        mainWorkbookDataSheet.Range("A" & xlsmLastRow + Counter & ":W" & xlsmLastRow + Counter).Value = csvWorkbookDataSheet.Range(Cells(i, 1), Cells(i, 23)).Value
        Counter = Counter + 1
    End If
Next i

---

Think I figured it out!!!

Code:

---

Sub UpdateDataWithNewWI()
Dim oldWb As Scripting.File
Dim newWb As Scripting.File
Dim mainWorkbook As Workbook
Dim newWorkbook As Workbook
Dim mainWorkbookDataSheet As Worksheet
Dim csvWorkbookDataSheet As Worksheet
Dim OldRange As Integer
Dim NewRange As Integer
Dim ListOld(), ListNew(), csvMovingData(), xlsmMovingData()
Dim xlFile As Variant
Dim fso As New FileSystemObject


' Set workbook/worksheet variables
Set oldWb = fso.GetFile("Y:\8.  Departmental Materials (7yr RT)\Planning Ops\Reporting\DASH Data Entry\Wisconsin.xlsm")
Set mainWorkbook = Workbooks.Open(oldWb)
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)


'xlFile = Application.GetOpenFilename("All Excel Files (*.csv*)," & _
'"*.xls*", 1, "Select .csv File for Evaluation", "Open", False)
'xlFileName = fso.GetFileName(xlFile) ' Gets filename of chosen file
Set newWb = fso.GetFile("C:\Users\adeno\Desktop\Today's FTP\WI.csv")
Set newWorkbook = Workbooks.Open(newWb) ' Opens .mtwData Workbook




' Set workbook/worksheet variables
'Set newWorkbook = Workbooks(xlFileName)
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)


xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 2 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row


With mainWorkbookDataSheet
    ListOld = Range(.Cells(xlsmFirstRow, 2), .Cells(xlsmLastRow, 2)).Value
End With
With csvWorkbookDataSheet
    ListNew = Range(.Cells(csvFirstRow, 2), .Cells(csvLastRow, 2)).Value
End With


Index = 0
Counter = 1
For I = LBound(ListNew) To UBound(ListNew)
    Found = False
    For j = LBound(ListOld) To UBound(ListOld)
        If ListNew(I, 1) = ListOld(j, 1) Then
            mainWorkbookDataSheet.Range("O" & j + 1 & ":W" & j + 1).Value = csvWorkbookDataSheet.Range("O" & I + 1 & ":W" & I + 1).Value ' Sets xlsm row values (Column O:W) to csv values
            Found = True
        End If
    Next j
    If Found = False Then
        mainWorkbookDataSheet.Range("A" & xlsmLastRow + Counter & ":W" & xlsmLastRow + Counter).Value = csvWorkbookDataSheet.Range(Cells(I + 1, 1), Cells(I + 1, 23)).Value
        Counter = Counter + 1
    End If
Next I
newWorkbook.Close
mainWorkbook.Save
mainWorkbook.Close
End Sub

---

dbc, glad to hear back from you!

It appears as if your variable assignment is off since you moved some things around.

The line:

Code:

---

Set mainWorkbook = ActiveWorkbook

---

Sets the variable 'mainWorkbook' to the active workbook. Your x2FileName.Open statement should open the .xlsm, but isnt the .xlsm what the macro runs from? If so, it may try to open it, realize its open, and the active workbook may remain the .csv from ITS open statement - not sure.

I would modify the code to this, at least:

Code:

---

' Set workbook/worksheet variables
Set mainWorkbook = ThisWorkbook
Set mainWorkbookDataSheet = mainWorkbook.Sheets(1)


'Workbooks.Open("C:\Users\adeno\Desktop\Today's FTP\WI.csv") ' Opens .csv Workbook

Set newWorkbook = ActiveWorkbook
Set csvWorkbookDataSheet = newWorkbook.Sheets(1)


xlsmFirstRow = 2 ' Row after header in .xlsm
xlsmLastRow = mainWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
csvFirstRow = 2 ' No header
csvLastRow = csvWorkbookDataSheet.Cells.Find(What:="*", LookIn:=xlValues, SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

...

---

Anything after that line shouldn't need to be changed...

Also, are you running this from a different workbook, or is the macro executed from the .xlsm? I believe the latter, correct?

If so - this should work fine.

In terms of the network drive, that often causes issues.

Try this format, where you use the servername and share to open the workbook on the network drive:

Code:

---

Workbooks.Open FileName:= "\\SERVER01\ShareName\Today's FTP\WI.csv"

---

dbc, if you run into any further problems, reference my last post - glad you figured it out on your own, though!

I would still recommend mapping your network shared workbooks file path using the //server/share method, as if you try to run the macro from another PC whos network letter is set to something else - it will not work.

Come back with any questions as always!!

ALSO - if it prompts if you are 'sure' when you close the old workbook, and you'd rather it didn't, you can use this:

Code:

---

Application.DisplayAlerts = False
oldWorkbook.Close
Application.DisplayAlerts = True

---

Just thought that might help!