Results 1 to 14 of 14
  1. #1
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25

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

    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:

    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.
    Attached Files Attached Files
    Last edited by rustynails; 11-14-2013 at 12:27 PM. Reason: Added modified Schedule.xlsx file

  2. #2
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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).

  3. #3
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    Quote Originally Posted by IslandHydro View Post
    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.

  4. #4
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    What does your desired output table(s) look like?

  5. #5
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    Quote Originally Posted by IslandHydro View Post
    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
    Attached Files Attached Files

  6. #6
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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

  7. #7
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    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

  8. #8
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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..

  9. #9
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    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.

  10. #10
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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

  11. #11
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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

  12. #12
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    Quote Originally Posted by IslandHydro View Post
    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!!!!!!!

  13. #13
    IslandHydro is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Oct 2013
    Location
    Whidbey Island, Washington State, USA
    Posts
    21
    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.

  14. #14
    rustynails is offline Novice
    Windows 8 Access 2007
    Join Date
    Jul 2013
    Posts
    25
    Quote Originally Posted by IslandHydro View Post
    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 !

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 08-08-2013, 03:54 PM
  2. Replies: 4
    Last Post: 12-17-2012, 01:21 PM
  3. Replies: 2
    Last Post: 08-14-2012, 04:24 AM
  4. Append to SQL table failing on data type
    By tpcervelo in forum Queries
    Replies: 2
    Last Post: 10-13-2011, 12:07 PM
  5. Replies: 1
    Last Post: 11-21-2010, 10:26 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums