Consulting

Results 1 to 14 of 14

Thread: Updating a master sheet from nightly data

  1. #1
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location

    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

  2. #2
    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!

  3. #3
    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.

  4. #4
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    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:

    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

  5. #5
    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'


    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
    Last edited by mattreingold; 07-09-2018 at 08:49 AM. Reason: Added note

  6. #6
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    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.

  7. #7
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    I'm getting "Object Variable or With Variable not set" for the line:

    rowIdx = x.Row ' Gets row index value

  8. #8
    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.

  9. #9
    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.
    Last edited by mattreingold; 07-09-2018 at 10:09 AM.

  10. #10
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    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

  11. #11
    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!

    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

  12. #12
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    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!

  13. #13
    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:
    exampleIL.jpg

  14. #14
    VBAX Regular
    Joined
    Jul 2018
    Posts
    7
    Location
    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):

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •