pasting recordset from access into excel

[megatronixs]

Hi all,

I have tried without luck to paste a recordset into excel in one sheet and then moving it out from there (cuting) and paste it to a second sheet.
The main idea is to use the query results to an excel sheet, then activate the excel sheet, cut it from there and move it to the second sheet. For now I managed to cut it off, but I'm not able to paste it to the second sheet.
The steps I wanted was to create a excel workbook, open it, paste the recordset into it, cut it and move it to the sheet 2 (needs to be created first). The main loop in the access database will go to the next query output and then activate the workbook again, paste the recordset, cut it off and then move it to the sheet2 (I will use some code to find the last column to past it there).

So far I have the below code:

Private Sub query_results()
Dim sFile As String
Dim stDocName As String
Dim i As Integer
Dim FinalRow As Long
Dim xlApp As Object
Dim xlSheet As Object
　
Set xlApp = CreateObject("Excel.Application")
Set xlSheet = xlApp.Workbooks.Open(sFile).Sheets(1)
xlApp.Visible = True
Worksheets.Add
FinalRow = Cells(Rows.Count, 2).End(xlUp).Row
　
　
sFile = "C:\Query\QueryResults.xls"
stDocName = "Query_Run1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query_Run1", ":\Query\QueryResults.xls"
Range("A1:G" & FinalRow).Cut
Range("A1").Select
With Selection
Cells(1, 1).PasteSpecial
End With
End Sub

It seems something easy, but I just don't get it working.

I posted it on this site also when I had the first try, but for some strange reason my IP address got blocked and I can't use the forum anymore :-(
http://www.accessforums.net/modules/using-loop-get-content-table-4-fields-56611/index2.html

Greetings.

 

[Ranman256]

Just do 2 transferspreadsheets.
Be sure to use 2 different tab names, (the param after the file name)

Docmd.transferspreadsheet acexport. 8, qry1, "c:\filename.xls", "sheet1"
docmd.transferspreadsheet acexport. 8, qry1, "c:\filename.xls", "sheet2"

 

[megatronixs]

Hi Rannman256,

I need to have them all in the second sheet. Every time the loops runs, it will create a second query results and then they want them all the results into the second sheet with one empty column space between them.
I managed to find some code to find the last used column, so it will find the last column used and then paste the next query results into the second empty one (so, I have one free column between results).

Greetings.

 

[megatronixs]

Hi,
One more thing that came to my mind, there are only 2 queries that are run from a loop (the difference is only the branch that is different). I use firt one query to create the variables to use them in a query as the criteria that comes from a table where it is used to build query1 or query2. it will loop trough all the records till the end and every time depending on the branch nr it will find, it will run query1 or query2

Greetings.

 

[Frothingslosh]

Try using CopyFromRecordset. It places the first field of the current record in the top left cell in the selected range, and fills in from there to the end of the recordset.

 

[JHB]

Where does the code run from, in Excel or MS-Access?
If MS-Access, you're missing all the references to the Excel object you create.

 

[Frothingslosh]

Where does the code run from, in Excel or MS-Access?
If MS-Access, you're missing all the references to the Excel object you create.

Edit: Nix that, I see what you're talking about:

sFile = "C:\Query\QueryResults.xls"
stDocName = "Query_Run1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, "Query_Run1", ":\Query\QueryResults.xls"
[COLOR="Red"]Range("A1:G" & FinalRow).Cut[/COLOR]
[COLOR="red"]Range("A1").Select[/COLOR]
With [COLOR="red"]Selection[/COLOR]
[COLOR="red"]Cells(1, 1).PasteSpecial[/COLOR]
End With
End Sub

OP, the items in red need xlSheet preceding them. Also, you may find your code easier to read and debug if you indent properly.

 

[JHB]

Also set the reference to the Excel Object library.

 

[Frothingslosh]

Also set the reference to the Excel Object library.

Meh, he's late binding. Perfectly valid.

 

[megatronixs]

Hi all,

I know, my code is a mess now and still need to figure out how to make some things work and then cleaning up the code.

Can I use CopyFromRecordset to paste the records from a query (query_run1) as in the above code, or do I need to create a SQL string to get the data into a recordset first.

I'm kind of confused at the moment :-(

Greetings.

 

[megatronixs]

Hi all,

I made some changes to the code and addes some things you suggested:

Private Sub btn_batch_process_Click()
Dim db As DAO.Database
Set db = CurrentDb
Dim rs As DAO.Recordset
Dim from_date As String
Dim to_date As String
Dim branch As String
Dim account As String
'Dim strSQL As String
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As DAO.Recordset

Set objXL = CreateObject("Excel.Application")
objXL.Visible = True
Set xlWB = objXL.Workbooks.Open("C:\Temp\MyBook.xls")
Set xlWS = xlWB.Worksheets("Sheet1")
Set db = CurrentDb
    Set rs = db.OpenRecordset("SELECT from_date, to_date, branch, account FROM tbl_batch_process", dbOpenDynaset)

    rs.MoveFirst
Do Until rs.EOF
from_date_txt = rs![from_date]
to_date_txt = rs![to_date]
branch_txt = rs![branch]
account_txt = rs![account]
 
If Me.branch_txt Like "980*" Then
    Set rst = CurrentDb.OpenRecordset("Query_Run1")
        Else
    Set rst = CurrentDb.OpenRecordset("Query_Run2")
End If
 
If rs.EOF = True Then
    Set rs = Nothing    'Release machine's memory
        Set db = Nothing
    Exit Sub
End If

'run code her for loop
 

    rs.MoveNext
    
Loop
rs.Close 'Close recordset when you are done.
Set rs = Nothing
Set db = Nothing
End Sub

I guess I can set the query to run if the branch nr is starting with 980, but when I get to the first IF, it errors with 3061 "too few parameters. Expected 4. (but I already pass them to the query before)

Any clue where I went wrong?

Greetings.

 

[megatronixs]

Hi all,

One thing that I noticed is that "CopyFromRecordset" does not add the headers to the excel sheet. This means I need to add more code to run with headers.

Greetings.

 

[Frothingslosh]

Well, for starters, you can use a query. One thing you need to know is that queries ARE SQL statements; what you're thinking of as a 'query' is actually the Query By Example grid, which is literally just a graphical UI for writing SQL statements. Also, creating the SQL statement at runtime, while sometimes necessary, actually causes a bit of bloating. Not much at any given time, but it's something to be aware of.

To use a query, you can either open the recordset based on an existing query using CurrentDB.OpenRecordset("QueryNameHere"), or else assign the query to a DAO.QueryDef object and then use OpenRecordset on that object. (That's how you handle parameter queries.)

Now, one way to handle headers is this:

First, to your declarations, add Dim fld as DAO.Field and Dim x as Long. Then set x = 1. (It's a column counter.)

Now, add the following immediately after your 'rs.MoveFirst' command:

    For Each fld In rs.Fields
        xlWS.Cells(1, x).Value = fld.Name
        x = x + 1
    Next fld

That will place your headers on row 1. You'll need to then have your procedure select cell A2 before executing CopyFromRecordset.

Before I can address your other issue, I need to know what's going on with the text box reads, the multiple queries, and the like. None of those appeared in your original version.

 

[megatronixs]

Hi,
My first post where a kind of mess, I started just willing to do something, but not knowing how to start it and later the requesters where changing a few times their mind.

There are only 3 queries in the database. The first query is to get the criteria to use on the 2 other queries.
The first query takes his criteria looping trough the records that are in the table "tbl_batch_process"
This is passed to the userform and the query 1 and query 2 take the criteria from there to build the resulst I need to pass to the excel sheet.

So, the loop produces results that goes to sheet1. Now I need to have the second results to be pasted on the same sheet1, but leaving one empty column and placing it on the second free column (this way I will have 1 free column between results).

Maybe I´m trying to explain it to complicated.

Greetings.

 

[Frothingslosh]

CopyFromRecordset allows you to specify where on the worksheet the output is placed.

Use a variable to hold the number of the first column to be used for each query. Create the first header starting in cell A1, use CopyFromRecordset to drop the recordset with cell A2 as the starting point. Then reset that column pointer to the column 2 after the end of the first query (I'll just call it Counter here). Drop a new set of headers starting in cell Counter,1, and then use CopyFromRecordset to output the second query with Counter,2 as the first cell.

 

[megatronixs]

Hi,

Thanks a lot

I will try this out as soon as I can.

Greetings.

 

[megatronixs]

Hi,

I finally made it work
Just few more things to fine-tune the queries and I´m done.

I will try to post all the code once I´m back at work so I can share this with others.

Greetings.