0

So I'm using vba in Access to generate an Excel sheet and then add a couple pages, count some data, and add some charts. To code faster I had been developing in Excel vba. Once I got everything printing out correctly, I copied over to Access and added the object references to make it work. That's all fine.

But. When I ran the code in Access, what took <2 seconds in Excel took 5+minutes in Access (I eventually gave up and just stopped the code bc it was taking too long). My problem is that I had been running a DoWhile Loop on all the rows in a sheet, where on each row I checked the value of a cell and then incremented a variable or added a row value to a variable. It seems like when I'm running it in Access there are the extra steps of: going to the Excel; finding the Do condition value; bringing the value back to Access; going to the Excel; retrieving the row value; bringing the value back to Access; going to the Excel; retrieving another row value; brining the latest value back to Access...

Is there any way to speed this up? To maybe push these condition checks/calculations into the Excel local rather than the Access local? To, idk, generate a .txt file that I could import into the excel workbook as a macro and then run that macro? I could probably sit down and scrap all my calculation code and figure out how to completely redo it, but am not looking forward to the lost time.

Edit:

Dim xl as object, wb as object, ws as object
Set xl = CreateObject("Excel.Application")
xl.Visible = False
xl.displayalerts = False
set wb = xl.workbooks.Open(wbookpath)

Dim cat1() As Variant, cat2() As Variant, cat3() As Variant, cat4() As Variant, cat5() As Variant
'arrlen is currently 8 but might change over time
ReDim cat1(1 To arrlen, 12)
ReDim cat2(1 To arrlen, 12)
ReDim cat3(1 To arrlen, 12)
ReDim cat4(1 To arrlen, 12)
ReDim cat5(1 To arrlen, 12)

'the slow part. this only takes 2 seconds if run in excel but 5+ minutes in access
For i = 1 To arrlen
    Set ws = wb.Sheets(i)
    cat1(i, 0) = ws.Name: cat2(i, 0) = ws.Name: cat3(i, 0) = ws.Name
    cat4(i, 0) = ws.Name: cat5(i, 0) = ws.Name
        
    r = 2 'row index
    Do Until ws.Cells(r, 5).Value = ""
    'column 5 will always have a date in it, so once it is empty there is no more rows
        m = DatePart("m", ws.Cells(r, 5))
        Select Case ws.Cells(r, 10).Value
            Case "History", "Orders"
                If cat1(i, m) = "" Then cat1(i, m) = 0
                cat1(i, m) = cat1(i, m) + 1
            Case "Rct to Inventory"
                If cat2(i, m) = "" Then cat2(i, m) = 0
                cat2(i, m) = cat2(i, m) + 1
            Case "Picklist"
                If cat3(i, m) = "" Then cat3(i, m) = 0
                cat3(i, m) = cat3(i, m) + ws.Cells(r, 3).Value
            Case "Work Order"
                If cat4(i, m) = "" Then cat4(i, m) = 0
                cat4(i, m) = cat4(i, m) + 1
            Case "Sealed"
                If cat5(i, m) = "" Then cat5(i, m) = 0
                cat5(i, m) = cat5(i, m) + 1
        End Select
        r = r + 1
    Loop
Next
L Kadue
  • 81
  • 1
  • 10
  • 4
    Nobody is able to help you if only tell us a story. Please, edit your question and post the code in discussion. If you do not work with an Excel already open session, basically, the difference should be because of the new Excel session process starting and the necessary workbook opening, if it is not created in code. It should work similarly if the variables ar properly declared. Anyhow, usually the code can be optimized even being handled from Access... – FaneDuru Aug 09 '21 at 20:11
  • 2
    If you post the parts of your code which make the most calls to access values from the worksheet, I'm sure folks will have suggestions for improvements. – Tim Williams Aug 09 '21 at 21:29
  • You can [link directly to an Excel file as a data source](https://support.microsoft.com/en-us/office/import-or-link-to-data-in-an-excel-workbook-a1952878-7c58-47b1-893d-e084913cc958) without opening Excel. – HackSlash Aug 09 '21 at 21:51
  • What you're doing looks like something that could be done with an Excel formula instead of VBA. Check out the [COUNTIFS](https://support.microsoft.com/en-us/office/countifs-function-dda3dc6e-f74e-4aee-88bc-aa8c2a866842) command. – HackSlash Aug 12 '21 at 16:14
  • 1
    `cat1(i, 0) = ws.Name: cat2(i, 0) = ws.Name: cat3(i, 0) = ws.Name` looks like it's going to the worksheet each time. Might be a little faster to set cat2, cat3, etc to equal cat1 so there's only one reference to the sheet. – Darren Bartrup-Cook Aug 13 '21 at 07:20
  • I don't see anything in the code that would cause the slow execution. -- The usual suggestion with this sort of function is: don't read single cells from the sheet, instead read the whole range (e.g. `ws.UsedRange`) into an array, see https://stackoverflow.com/questions/37689847/creating-an-array-from-a-range-in-vba and then work with the array. – Andre Aug 13 '21 at 08:27

0 Answers0