Export query results to specific cell in Excel

[robbydogg]

Hello,

I have spent most of today trying to find the answer, attempting different method and searching for all i'm worth but nothing so far has worked.

I have a query called
Qry_Output - which has 2 columns - Reference_Name and Value
which i would like to be able to click a button and it send the results from that query to the excel sheet,
C:\Documents and Settings\FOX7HR5\Desktop\New Microsoft Excel Worksheet.xls
and paste it in the sheet called Testing at Cell B6 onwards (and sideways)

the last method i tried game me a debug error, "Runtime error 3125
'Testing$C4:30' is not a valid name. Make sure that it does not include invalid characters or punctuation and that it is not too long.".

here's the code:

DoCmd.TransferSpreadsheet acExport, 8, "Qry_Output", "C:\Documents and Settings\FOX7HR5\Desktop\New Microsoft Excel Worksheet.xls", True, "Testing!C4:D30"

i have also tried a lot of other methods mentioned on the forums but none have worked so far
I tried the one using a module to export and still couldn't get it working.

the closest i got was using Bob Larson's module and the following code, but it doesn't output the query results, just the query name.

Private Sub Command25_Click()
Dim objXL As Object
Dim objWB As Object
Dim objWS As Object
Set objXL = CreateObject("Excel.Application")
Set objWB = objXL.Workbooks.Open("C:\Documents and Settings\FOX7HR5\Desktop\New Microsoft Excel Workshee1.xls")
Set objWS = objWB.Worksheets("Sheet1")
With objWS
.Cells(3, 3).Value = "qry_output"
End With
objXL.Visible = True
End Sub

any ideas would be muchly appreciated.

 

[G81]

have you tried using a recordset? Run the query in vba and then save the value into a string variant. Then use that, rather than:

.Cells(3, 3).Value = "qry_output"

 

[robbydogg]

hi,

i haven't even got a clue how to do that

the query i'm running is as follows:

SELECT tbl_EPOutput.EPREF, tbl_EPOutput.EPValue
FROM tbl_EPOutput;
union
SELECT tbl_ONSGIOutput.ONSGIRef, tbl_ONSGIOutput.ONSGIValue
FROM tbl_ONSGIOutput;
union
SELECT tbl_Output.OSPREF, tbl_Output.OSPValue
FROM tbl_Output;
UNION SELECT Tbl_SGAOutput.SGARef, Tbl_SGAOutput.SGAValue
FROM Tbl_SGAOutput;

 

[robbydogg]

Right - got it sorted.

Here's how i did it in easy steps for anyone else to use.

1. Open up the excel document you want to export the data to.
select the area you wish for the data to be pasted in

In the top left there will a cell naming box, you can name this selected are anything you like (i have used 'Ranger')

now in the code for what ever you want to run the extract (i have used a command button) put in this code:

DoCmd.TransferSpreadsheet acExport, 8, "ENTER QUERY NAME HERE", "ENTER FULL XLS FILE PATH HERE", True, "ENTER EXCEL CELL RANGE HERE"

as an example below, here's my exact code

DoCmd.TransferSpreadsheet acExport, 8, "Qry_Output", "C:\Documents and Settings\Desktop\New Microsoft Excel Workshee1.xls", True, "Ranger"


and bam - every time the new data will be pasted over the old.

I hope this helps anyone who has been searching for this like myself

 

[G81]

cool, glad you got it sorted.

Also if you ever want to go along your original route from within access vba. To send a single record to a variant from a recordset (If multiple records returned you will need to loop through them and save them to an array):

dim rs as dao.recordset
dim db as database
dim str as string
dim abc as string
 
set db = currentdb()
str = "SELECT yourfieldname FROM yourtablename WHERE yourcriteria"
set rs = db.openrecordset(str)
abc = rs("yourfieldname")

 

[rlewis76]

Right - got it sorted.

Here's how i did it in easy steps for anyone else to use.

1. Open up the excel document you want to export the data to.
select the area you wish for the data to be pasted in

In the top left there will a cell naming box, you can name this selected are anything you like (i have used 'Ranger')

now in the code for what ever you want to run the extract (i have used a command button) put in this code:

DoCmd.TransferSpreadsheet acExport, 8, "ENTER QUERY NAME HERE", "ENTER FULL XLS FILE PATH HERE", True, "ENTER EXCEL CELL RANGE HERE"

as an example below, here's my exact code

DoCmd.TransferSpreadsheet acExport, 8, "Qry_Output", "C:\Documents and Settings\Desktop\New Microsoft Excel Workshee1.xls", True, "Ranger"


and bam - every time the new data will be pasted over the old.

I hope this helps anyone who has been searching for this like myself

Perfect! Just what i was after, thanks very much!

 

[sxschech]

Another approach is to use copyfromrecordset. This will take all the data without needing to set up a loop and paste it into excel beginning at the cell position specifified (no need to define a name range). Another interesting thing about using copyfromrecordset is that if you have formatting, the pasted data will maintain the preexisting format. However, it does not bring over the field names only the data. If you need fieldnames, this link shows how to set up a loop to obtain them.

https://msdn.microsoft.com/en-us/library/office/ff839240.aspx

Here is a sample:
I copied parts of existing code, so this won't work on it's own, but gives an idea how to set up.
This puts the query results in the third row beginning at col 1.

Dim rs As DAO.Recordset
    Set objapp = CreateObject("Excel.Application")
    objapp.Visible = True
    Set wb = objapp.Workbooks.Open(Filename, True, False)
    Set rs = CurrentDb.OpenRecordset("YourQuery")
    For Each ws In wb.Worksheets
        With ws
            .Activate
            .Cells(3, 1).CopyFromRecordset rs
        End With
    Next
    rs.Close
    Set rs = Nothing
    objapp.Quit
    Set objapp = Nothing