Loop to copy data from from one workbook to another, then import into access failing

[rustynails]

I have two workbooks: Schedule.xlsx (wkb1) which contains data and Temp.xls (wkb2), which stores temporary data. Schedule.xlsx is basically a calender that goes across for each Aircraft Type (AC Type). So it looks like:




AC01 Nov 1 data Nov 2 Data.....Nov 30 data
AC02 Nov 1 data Nov 2 Data.....Nov 30 data
...
AC40 Nov 1 data Nov 2 Data.....Nov 30 data

I have attached the Schedule.xlsx file.

Mind you the "data" above is a 7x9 table for each day. We want those in a table in Access. My VBA code copies that specific window range data from Schedule into temp file. Then, the VBA code transfers the data in temp file into a temp table in access. A query will append what ever is in temp table into a staging table. The problem is, VBA keeps copying-pasting only the first range specified in the first iteration of the loop. Theoretically after the first iteration, it should move to a new range, copy paste that. Then in 3rd iteration, move to a new range, copy paste that, and so on. I understand that this is kinda long winded, but I put lots of descriptive comments in the code below. But any help is appreciated. Here's the code:

'''Workbook Operations

Set wkb1 = Workbooks.Open(FileName:=InputFolder & "Schedule.xlsx")
Set wkb2 = Workbooks.Open(FileName:=InputFolder & "temp.xls")
wkb1.Activate
Set wksh1 = wkb1.Sheets("Sheet1")
Set wksh2 = wkb2.Sheets("Sheet1")

''Get Last Row and Last Column in Schedule file
LastRow = wkb1.ActiveSheet.UsedRange.Rows.Count
LastColumn = wkb1.ActiveSheet.Cells(1, Columns.Count).End(xlToLeft).Column

''Initialization of variables. x and i are for the For Loop, but the range I want is specified in StartRow, StartCol, EndRow and EndCol. You will see in a second below in the For Loop
StartRow = 32
StartCol = 9
EndRow = 40
EndCol = 17
i = 32
x = 9

'Outer Loop for Row Range. Move vertical until conditions are met
wkb1.Activate
For i = StartRow To LastRow

'Inner Loop for Column Range. Move Horizontal until conditions are met
    For x = StartCol To LastColumn

'This is where I'm copying the range
        wksh1.Range(Cells(StartRow, StartCol), Cells(EndRow, EndCol)).Copy
'Also copying some data in variables
        ACType = ActiveSheet.Cells(StartRow, 1).Value
        Pax = ActiveSheet.Cells(StartRow, 2).Value
        SchDate = ActiveSheet.Cells(30, StartCol).Value
'Pasting the range into a new workbook.
        wkb2.Activate
        wksh2.Range(Cells(2, 3), Cells(10, 11)).Select
        ActiveSheet.Paste
        LastRow2 = wkb2.ActiveSheet.UsedRange.Rows.Count
        wkb2.Save
        wkb2.Close
'Here, transfer the temp file into a a table called TEMP_UPTAKE_WEEK_STAGING        
        DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TEMP_UPTAKE_WEEK_STAGING", InputFolder & FileName2, True, "A1:L" & LastRow2 & ""
'Update the data with variables. Not important
        DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET ACTYPE = '" & ACType & "'"
        DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET SCHDATE = '" & SchDate & "'"
        DoCmd.RunSQL "UPDATE TEMP_UPTAKE_WEEK_STAGING SET Pax = '" & Pax & "'"
'Append temp table to staging table.
        DoCmd.OpenQuery "APPEND_TEMP_UPTAKE_WEEK_TO_STAGING"
        
        Debug.Print "Schedule Date: " & SchDate & " AC Type: " & ACType & " PAX: " & Pax & " StartRow: " & StartRow & " StartCol: " & StartCol & " EndRow: " & EndRow & " EndCol: " & EndCol
        
'This is the part where it should move to the next range, but it's not!
        StartCol = StartCol + 15
        EndCol = EndCol + 15
        x = StartCol
        DoCmd.RunSQL "DELETE FROM TEMP_UPTAKE_WEEK_STAGING"
        
'Just a quick way to exit the process
        If ACType = "AC03" Then
        Exit Sub
        End If
        
        
    Next x

'Move to a new set that is 10 rows below.
    
    StartRow = StartRow + 10
    i = StartRow
    StartCol = 9
    EndCol = 17
Next i

My Access database for weird reason does not work if I remove everything and only keep the relevant bits. I can attach the one I have, but the problem is that it is looking for specific folders which you will need to create, so I was trying to avoid that

I hope the error is simple like opening or closing a workbook. Any help is super appreciated.

[IslandHydro]

Why deal with temp spreadsheet at all? Seems like you should be able to just walk through the Schedule (looping on Plane, Date, and Flight), and directly create records in the access table(s).

[rustynails]

Why deal with temp spreadsheet at all? Seems like you should be able to just walk through the Schedule (looping on Plane, Date, and Flight), and directly create records in the access table(s).

I would love to find an easier solution, but this is what I could come up with It is how I picturized the solution in my head.

[IslandHydro]

What does your desired output table(s) look like?

[rustynails]

What does your desired output table(s) look like?

I have attached the database file that has the output table with the desired results. IDEALLY I want it to look like this (just a few more columns copied from excel from the same row). I also attached the Schedule.xlsx with some changes (it had some wrong values, added some field names here and there). Also attached an mdb version if anyone wants to look at it.

Basically the idea is to grab all the data in a calender day, and put it into an access table. Repeat it for all calender days. There are some fields like ACType, PAX and Schedule Date that are not on the same row which also need to be added.

Edit: Please not the Schedule.zip has updated data

[IslandHydro]

Try this:

Dim wkb1 As Excel.Workbook, wksh1 As Excel.Worksheet, OutTable As Recordset, AcType As String, PaxCount As String
Dim SchDateStr As String, Row As Integer, Col As Integer, AirRow As Integer, DateCol As Integer

Set wkb1 = Workbooks.Open("C:\Users\d.kelly\Desktop\Schedule. xlsx")
Set wksh1 = wkb1.Sheets("Sheet1")

DoCmd.RunSQL ("Delete * from Schedule")
Set OutTable = CurrentDb.OpenRecordset("Schedule", dbOpenDynaset)

AirRow = 0
Row = 32 + (AirRow * 10)
AcType = wksh1.Cells(Row, 1).Value

While AcType <> ""
PaxCount = wksh1.Cells(Row, 2)
DateCol = 0
Col = 9 + (DateCol * 15)
SchDateStr = wksh1.Cells(30, Col)
While SchDateStr <> ""
If IsDate(SchDateStr) Then
OutTable.AddNew
OutTable("ACType") = AcType
OutTable("Pax Count") = PaxCount
OutTable("Sch Date") = CDate(SchDateStr)
OutTable("Unique Uplifts") = wksh1.Cells(Row, Col - 5)
OutTable("Strips") = wksh1.Cells(Row, Col - 4)
OutTable("Flight Count") = wksh1.Cells(Row, Col - 3)
OutTable("US") = wksh1.Cells(Row, Col - 2)
OutTable("24 to 42") = wksh1.Cells(Row, Col - 1)
OutTable("Flight Number") = wksh1.Cells(Row, Col)
OutTable("ORG") = wksh1.Cells(Row, Col + 1)
If IsDate(wksh1.Cells(Row, Col + 2)) Then OutTable("ETD") = wksh1.Cells(Row, Col + 2)
If IsDate(wksh1.Cells(Row, Col + 3)) Then OutTable("ETA") = wksh1.Cells(Row, Col + 3)
OutTable("Dest") = wksh1.Cells(Row, Col + 4)
If IsDate(wksh1.Cells(Row, Col + 5)) Then OutTable("GT") = wksh1.Cells(Row, Col + 5)
If IsDate(wksh1.Cells(Row, Col + 6)) Then OutTable("FT") = wksh1.Cells(Row, Col + 6)
If IsDate(wksh1.Cells(Row, Col + 7)) Then OutTable("BT") = wksh1.Cells(Row, Col + 7)
If IsDate(wksh1.Cells(Row, Col + 8)) Then OutTable("Fresh Time") = wksh1.Cells(Row, Col + 8)
OutTable.Update
End If
DateCol = DateCol + 1
Col = 9 + (DateCol * 15)
SchDateStr = wksh1.Cells(30, Col)
Wend
AirRow = AirRow + 1
Row = 32 + (AirRow * 10)
AcType = wksh1.Cells(Row, 1)
Wend
Set OutTable = Nothing
wkb1.Close

[rustynails]

IslandHydro, you're a pro!

It worked. Thank you. Only thing is that I removed the if statements for the date/time fields to get everything into the DB. After that I ran a query to format them. Otherwise the VBA code was dropping them. Again, thank you x 1000

[IslandHydro]

note the line that is as follows:

DoCmd.RunSQL ("Delete * from Schedule")

that clears the schedule table, dont know if you want to do that or not..

[rustynails]

Oops spoke too soon I just got happy that all of it got imported and made the post!

But I just noticed that the code is picking up only the first row of each AC Type. If you look at the Schedule.xlsx, each ACType has about 10 records attached to it. For Example, for 11/1/2013, AC01, it is only picking up the row with Flight Number 1. It should pick up the flight numbers 1, 2, 3, 4, 5 and 6 which are in next rows. Let me know if I am not clear.

[IslandHydro]

Sure enough, added a couple lines:

Dim wkb1 As Excel.Workbook, wksh1 As Excel.Worksheet, OutTable As Recordset, AcType As String, PaxCount As String
Dim SchDateStr As String, Row As Integer, Col As Integer, AirRow As Integer, DateCol As Integer, Irow As Integer

Set wkb1 = Workbooks.Open("C:\Users\d.kelly\Desktop\Schedule. xlsx")
Set wksh1 = wkb1.Sheets("Sheet1")

DoCmd.RunSQL ("Delete * from Schedule")
Set OutTable = CurrentDb.OpenRecordset("Schedule", dbOpenDynaset)

AirRow = 0
Row = 32 + (AirRow * 10)
AcType = wksh1.Cells(Row, 1).Value

While AcType <> ""
PaxCount = wksh1.Cells(Row, 2)
DateCol = 0
Col = 9 + (DateCol * 15)
SchDateStr = wksh1.Cells(30, Col)
While SchDateStr <> ""
If IsDate(SchDateStr) Then
For Irow = 0 To 9
OutTable.AddNew
OutTable("ACType") = AcType
OutTable("Pax Count") = PaxCount
OutTable("Sch Date") = CDate(SchDateStr)
OutTable("Unique Uplifts") = wksh1.Cells(Row + Irow, Col - 5)
OutTable("Strips") = wksh1.Cells(Row + Irow, Col - 4)
OutTable("Flight Count") = wksh1.Cells(Row + Irow, Col - 3)
OutTable("US") = wksh1.Cells(Row + Irow, Col - 2)
OutTable("24 to 42") = wksh1.Cells(Row + Irow, Col - 1)
OutTable("Flight Number") = wksh1.Cells(Row + Irow, Col)
OutTable("ORG") = wksh1.Cells(Row + Irow, Col + 1)
If IsDate(wksh1.Cells(Row + Irow, Col + 2)) Then OutTable("ETD") = wksh1.Cells(Row + Irow, Col + 2)
If IsDate(wksh1.Cells(Row + Irow, Col + 3)) Then OutTable("ETA") = wksh1.Cells(Row + Irow, Col + 3)
OutTable("Dest") = wksh1.Cells(Row + Irow, Col + 4)
If IsDate(wksh1.Cells(Row + Irow, Col + 5)) Then OutTable("GT") = wksh1.Cells(Row + Irow, Col + 5)
If IsDate(wksh1.Cells(Row + Irow, Col + 6)) Then OutTable("FT") = wksh1.Cells(Row + Irow, Col + 6)
If IsDate(wksh1.Cells(Row + Irow, Col + 7)) Then OutTable("BT") = wksh1.Cells(Row + Irow, Col + 7)
If IsDate(wksh1.Cells(Row + Irow, Col + 8)) Then OutTable("Fresh Time") = wksh1.Cells(Row + Irow, Col + 8)
If wksh1.Cells(Row + Irow, Col + 1) <> "" Then OutTable.Update
Next Irow
End If
DateCol = DateCol + 1
Col = 9 + (DateCol * 15)
SchDateStr = wksh1.Cells(30, Col)
Wend
AirRow = AirRow + 1
Row = 32 + (AirRow * 10)
AcType = wksh1.Cells(Row, 1)
Wend
Set OutTable = Nothing
wkb1.Close

[IslandHydro]

Note this line:

If wksh1.Cells(Row + Irow, Col + 1) <> "" Then OutTable.Update

I keyed on whether or not the ORD field was filled in to decide whether or not to store the record, in order to skip the blank data rows

[rustynails]

Note this line:

If wksh1.Cells(Row + Irow, Col + 1) <> "" Then OutTable.Update

I keyed on whether or not the ORD field was filled in to decide whether or not to store the record, in order to skip the blank data rows

Awesome. It's looking good so far Nice work IslandHydro

I had a question about your code. What is AirRow doing? Also, when we go into nested ifs and while statements, do the variables get re-initialized, or do they carry over from the previous loop? So the variable Row is used in both outer while loop as well as inner For loop, and I was trying to understand if their values carry over after each iteration. Thanks again!!!!!!!

[IslandHydro]

AirRow walks through the aircraft blocks of 10 rows each. The Row variable simply points at the top row for any aircraft; Row = 32 + (AirRow*10). It does not get reinitialized on the inner loops. A great way to understand how code works is to step the code forward (via multiple presses of F8). As you walk through the code, you can hover your mouse over a variable and Access will tell you what the variable value is. This is very helpful for debugging this sort of code.

[rustynails]

AirRow walks through the aircraft blocks of 10 rows each. The Row variable simply points at the top row for any aircraft; Row = 32 + (AirRow*10). It does not get reinitialized on the inner loops. A great way to understand how code works is to step the code forward (via multiple presses of F8). As you walk through the code, you can hover your mouse over a variable and Access will tell you what the variable value is. This is very helpful for debugging this sort of code.

I will keep that in mind. Thank you once again !