creating multiple queries in vba

[missprogrammer]

I am new to vba and I want to create multiple queries in vba using ado connection and export the results into excel spreadsheet.

 

[Dutt]

Try this you might need to modify it a bit

Dim xlApp As Object ' Declare variable to hold the reference.
Dim xlbk As Workbook 'Declare Excel Workbook and worksheet
Dim xlsht As Worksheet
Dim mydb As Database
Dim myrec As Recordset
Dim x As Integer
Dim MySQL As String
Set xlApp = CreateObject("Excel.Application&quot
Set xlbk = xlApp.Workbooks.Add
Set xlsht = xlbk.Worksheets(1)
Set mydb = CurrentDb()

MySQL = ("SELECT * FROM [Table Name]&quot'Enter Query Here


'Open the recordset with the mySQL Variable
Set myrec = mydb.OpenRecordset(MySQL)
myrec.MoveFirst
'entering data that fits criteria
Do Until myrec.EOF = True
xlsht.cells(x, 1) = myrec![Field Name]
xlsht.cells(x, 2) = myrec![Field Name]
xlsht.cells(x, 3) = myrec![Field Name]
x = x + 1
myrec.MoveNext
Loop
'pull up application with data
xlApp.Visible = True

You need to Reference the Excel Component as well

 

[missprogrammer]

Thank you so much Dutt, one more thing.
This is working find. I run this report weekly and I want to increase the processing time. I dont know if the way I did it the first time was the best way. But I created a connection to a SQL Database using Access. Would it have been better for me to use VBA using a sql to the sql tables... by doing so would this speed up my processing time.. I'm probably not making my self clear,,,,but thanks in advance.

 

[MichaelRed]

You are WAY to invested in Excel. Try Access without the annoyance of Excel and your life will be better.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over