Transfer data directly to Excel spreadsheet

  • Thread starter Thread starter Associates
  • Start date Start date
A

Associates

Hi,

I was wondering if there is a way of exporting data from query to excel
spreadsheet as shown in the code. The following code is to export the result
of the query to CSV type of format.

For example, i have a query (simple one) to retrieve students from student
table
strSQL = "SELECT Student_ID, First_Name, Last_Name, Department,
Semester1Mark"
strSQL = strSQL & " FROM Student"

rsNew.Open strSQL, CurrentProject.Connection, adOpenDynamic,
adLockOptimistic 'execute the query and open it for reading

I would like to open up a new excel spreadsheet and pour in the result to
it. Here is the code i have at the moment using CSV.

stFileName = "" & GetFileName(True, , "Select file")
myfiletemp = stFileName

'Write to a text file
Set FS = CreateObject("Scripting.FileSystemObject")
Set a = FS.CreateTextFile(myfiletemp, True)

It'd be great if we can transfer directly to excel the data and be able to
customise the spreadsheet in terms of where we'd like data be placed in the
spreadsheet.

BTW, i'm using Access 03 and Excel 03

Thank you in advance
 
Thank you all, Alex and Doug for your replies.

They are just what i need.

Thank you once again
 
Back
Top