Exporting query to excel using vba (1 Viewer)

CS45

Member
Local time
Today, 07:25
Joined
Jun 17, 2020
Messages
38
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 sheet...

The end goal is to write a VBScript to open access and export into excel, then run an excel macro to organize the data how I want all by using task scheduler. The excel macros are written already.
 

Minty

AWF VIP
Local time
Today, 11:25
Joined
Jul 26, 2013
Messages
10,354
You would have to open the existing excel file and find the last row, then you could copy from recordset into that location.
Certainly possible using VBA, and in fact, I'd just let Access manipulate the whole excel file using automation.

Something like this will work in Access
Code:
Sub GetLastRow(MyRange As Excel.Range)
    Dim lngLastRow As Long

    With MyRange.Worksheet
        lngLastRow = .Cells(.Rows.Count, MyRange.Column).End(xlUp).Row
      End With
End Sub
 

CS45

Member
Local time
Today, 07:25
Joined
Jun 17, 2020
Messages
38
You would have to open the existing excel file and find the last row, then you could copy from recordset into that location.
Certainly possible using VBA, and in fact, I'd just let Access manipulate the whole excel file using automation.

Something like this will work in Access
Code:
Sub GetLastRow(MyRange As Excel.Range)
    Dim lngLastRow As Long

    With MyRange.Worksheet
        lngLastRow = .Cells(.Rows.Count, MyRange.Column).End(xlUp).Row
      End With
End Sub

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!
 

Minty

AWF VIP
Local time
Today, 11:25
Joined
Jul 26, 2013
Messages
10,354
I think your terminology is slightly at crossed purposes here.
In Access, a macro is a different thing to VBA code. A macro is a simple set of visually created commands that don't have the same flexibility as VBA code.
In Excel, your macro creates VBA code.

Normally you attach code in access to an event. So on an Access form, you would place a command button and in the On_Click event of that command button, you would attach some VBA code or confusingly a macro. You can also create functions and call those in queries etc.

So whilst developing your solution you would create a simple form with a command button and the VBA code behind the command button would perform the excel open file, find the last row, run your pass though query and append your dataset to the end of the spreadsheet.

Press the button and watch the carnage :cool:

Now obviously if you break this down into baby steps it becomes easier.
So firstly just open your existing spreadsheet.
Then find the last row.
etc. etc.

There are quite a lot of examples of code on this site - simply look at the bottom of the thread.
 

CS45

Member
Local time
Today, 07:25
Joined
Jun 17, 2020
Messages
38
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 that? Do I need to imbed another sub and if so, how?
 

CS45

Member
Local time
Today, 07:25
Joined
Jun 17, 2020
Messages
38
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 that? Do I need to imbed another sub and if so, how?

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
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:25
Joined
Sep 21, 2011
Messages
14,038
I use the offset property.
However Minty's code gives you the row number so just increment that by 1 ?
 

Isaac

Lifelong Learner
Local time
Today, 04:25
Joined
Mar 14, 2017
Messages
8,738
credit to Minty, just adding this as per Op's last request :)
Code:
dim nextrow as long, ws as object
set ws = targetWorkbook.Worksheets("Sheet1")
nextrow = ws.range("A" & ws.rows.count).end( -4162 ).row + 1
targetWorkbook.worksheets(“Sheet1”).Range("A" & nextrow).CopyFromRecordset rsQuery

I would be more 'sure' of my code if I could see your whole code, thus knowing things like early/late binding, objects already declared, etc. but that's the idea IMO.
 

CS45

Member
Local time
Today, 07:25
Joined
Jun 17, 2020
Messages
38
credit to Minty, just adding this as per Op's last request :)
Code:
dim nextrow as long, ws as object
set ws = targetWorkbook.Worksheets("Sheet1")
nextrow = ws.range("A" & ws.rows.count).end( -4162 ).row + 1
targetWorkbook.worksheets(“Sheet1”).Range("A" & nextrow).CopyFromRecordset rsQuery

I would be more 'sure' of my code if I could see your whole code, thus knowing things like early/late binding, objects already declared, etc. but that's the idea IMO.
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?
 

Isaac

Lifelong Learner
Local time
Today, 04:25
Joined
Mar 14, 2017
Messages
8,738
-4162 is the same as xlUp, only you'd want to use the numeric equivalent (instead of xlUp), IF, you were using Late Binding to go from Access to Excel automation. You'll know which binding you are using depending on whether you have Excel reference libraries checked in tools > references along with Set statements that refer to Excel.Application, Excel.Workbook (early binding indication). Or if you have declared excel items as Objects, and set the application using CreateObject, and don't need Excel library references in tools > references (Late Binding).
Although technically, it is the Set statement that most strictly/theoretically determines the binding I believe, and unfortunately some people's code mixes and matches all of that with no apparent purpose.
Which is something I can't tell without seeing more of your code. So I figured the numeric equivalent would be safest, as it works on both bindings.
 

Isaac

Lifelong Learner
Local time
Today, 04:25
Joined
Mar 14, 2017
Messages
8,738
I just realized that I really should add one more important thing. The whole "xlUp" method of finding the last row, is only as good as you are sure that that particular column always contains data.
It's the same as putting your cursor on the millionth row in the sheet and keying Ctrl+Up Arrow. Which, if you did that on a sheet where the general data went down to row 20, but the data in column A only went down to row 19, then that action would take you up to 19, not 20.

So be sure that the column you reference in the line of code that has -4162 or xlUp, is a column that is guaranteed to be populated with data all the way down to the bottom of the data. It may not be A
 

Isaac

Lifelong Learner
Local time
Today, 04:25
Joined
Mar 14, 2017
Messages
8,738
Today in my own work I was reminded of a particular thing that can happen when using .End to determine that "last" row, or column, of data. There are scenarios whereby that method will take you to the edge of a previously used range which no longer has data (such as a Table that extends downward, empty, past the real filled rows of data). In that case the .End will take you to an empty cell actually, and ANOTHER .End is required to get all the way "up" (or "left"). Due to this, it's probably safer to use a method like this:

Code:
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
If Len(Cells(lastrow, 1).Value) = 0 Then
    'do it again, this time start from lastrow
    lastrow = ws.Range("A" & lastrow).End(xlUp).Row
End If

... Just thought I ought to come back and make the adjustment to my recommendation.
 

Users who are viewing this thread

Top Bottom