Search results

  1. C

    Using VBA to sort through a range for first cell with error

    Hi all, I am working on an Excel project for work in which I need to write a macro that, among other things, searches through a range of cells and returns the first cell that has a #DIV/0 error. That range calculates a division of cells within a pivot table. If the pivot table has 5 rows of...
  2. C

    Moving cursor after copyfromrecordset

    Hi all, I currently have an Access macro that uses CopyFromRecordset to copy query data to a specific cell in an Excel sheet. I now want to automatically run an Excel macro that manipulates that data. How do I move the cursor from A1 to the first cell of the new data, as that’s where my excel...
  3. C

    Exporting query to excel using vba

    Great I will try this. Sorry, I’m not sure how much of my code I can share for security reasons and I don’t want to test that boundary...out of curiosity, what does the -4162 do? Sheet1 has hundreds of thousands of rows of data right now, so would that cause problems?
  4. C

    Exporting query to excel using vba

    Minty may have done this above, but I’m not entirely sure how to imbed that sub into my function. That sub also finds the last used cell, so how do I move the active cell down one? Thanks
  5. C

    Exporting query to excel using vba

    Ok I’ve found a code that does almost everything I need. The last step is to find the last cell in a sheet. Currently, the line of code is: targetWorkbook.worksheets(“Sheet1”).Range(“A4”).CopyFromRecordset rsQuery instead of A4, I need it to point to the first empty cell in col A. How do I do...
  6. C

    Exporting query to excel using vba

    So would the macro run the above code or would it still be an export macro? And where would the above code go inside access? Can you explain the order of the process a bit more clearly, I’m pretty new with vba and access. Thanks!
  7. C

    Exporting query to excel using vba

    Hi all, I’ve written a pass-through query and an autoexec macro that runs said query and exports it to excel on startup. Is there a way to export the query data to an existing excel file at the bottom of the existing data? All I’ve been able to find is exporting it to a new file or a new...
  8. C

    Query data pull takes FOREVER

    It says that the query only pulls from one linked table...I keep getting missing expression errors in my pass through version of the select query.
  9. C

    Query data pull takes FOREVER

    What do you mean? And how do I check? I think the query pulls from the same table but it may not...
  10. C

    Query data pull takes FOREVER

    I can’t post any of my code for security reasons, sorry. On the note of the other thread, I tried creating a pass through query but I can’t get to the server to look at what the table’s fields are titled.
  11. C

    Query data pull takes FOREVER

    The 24 minute query returned 3554 records, the 1 minute returned 2091.
  12. C

    Query data pull takes FOREVER

    the difference was marginal. With total row it was 27 minutes (usually between 20 and 50). Without the row, it was 24 minutes. Any other tips that could drastically reduce that time? I run other queries with more data that take 1-2 minutes, so I don’t know why this one is so long...
  13. C

    Query data pull takes FOREVER

    Could I move the things in criteria (such as Like “COMPLETE”) into the Sort row?
  14. C

    Query data pull takes FOREVER

    Is the HAVING clause the same as the criteria? I know one is SQL and one is in design view but are they related? I ask because the internet is saying that the HAVING clause filters the data after it is grouped, but if I’m not grouping them that may be an issue...
  15. C

    Query data pull takes FOREVER

    I removed the totals ribbon and am currently running it again. I also have a few criteria that are necessary for the data I need; would those slow the pull down as well?
  16. C

    Query data pull takes FOREVER

    By aggregating the data, do you mean the “Group By” clause? If so then yes I do use that...I’m not quite sure hat it does but I did not write the query, just fixing it. What do the values in the “total” row do?
  17. C

    Query data pull takes FOREVER

    Hi all, I run an Access query every week that pulls data from a table that is linked to an external warehouse. The query selects a ton of data, but it takes about 40 minutes to execute. Is there any way to speed that up, or is it a bandwidth issue?
  18. C

    ODBC error on pass-through query

    View* not table as it turns out
  19. C

    ODBC error on pass-through query

    I think it may have to do with the ODBC driver but I’m not sure. Anyway, now it is saying that all my identifiers are invalid. Is there a similar way to see what identifiers the DB table uses?
  20. C

    ODBC error on pass-through query

    That worked much better! 🥳
Top Bottom