VBA Code to export query data from MS Access to Excel

[shams100]

I'm looking for an example VBA Code to export or transfer query data
from MS Access
to Excel.

I use this code:

Set xlApp = CreateObject("Excel.Application")
xlApp.Visible = True
Set xlBook = xlApp.Workbooks.Add
Set xlSheet = xlBook.Worksheets("Sheet1")
xlSheet.Name = "sheetName"


With xlApp
With xlSheet.QueryTables.Add(Connection:=MyConnect, _
Destination:=Range("A1"))
.CommandText = "My SQl query"
.Name = "clearingHouse"
.FieldNames = True
.BackgroundQuery = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
End With


Evry time I run it I got this message error after it open Excel
application and create the sheet with the name:

Method Range of Object_ global faild

Does anyone know wehere i can find some example VBA code to accomplish
this.
Thanks,

 

[Ken Snell [MVP]]

What version of ACCESS are you using? What is the SP number that you last
installed?

 

[shams100]

Ms 2003
sp1

 

[Ken Snell [MVP]]

ACCESS 2003 SP1 has not "lost" the ability to edit data in an EXCEL sheet,
but I admit that I'm not familiar with the code sequence that you posted --
it's much more EXCEL VBA than ACCESS VBA.

Have you tried using the TransferSpreadsheet method in ACCESS for creating a
new EXCEL workbook file from a query? Check it out in Help file. Post back
if that won't do what you need done.

 

[George Nicholson]

Method Range of Object_ global faild
indicates that the problem is in the line

Destination:=Range("A1"))

(since that's the only place you use the Range method)

You might try:
Destination:=xlSheet.Range("A1"))

"1004" errors (of which Method Range of Object_ global failed is a member)
are almost always caused because code was not specific enough for VB to act
upon. Like specifying a range and assuming VB would "know" which sheet you
meant. There are exceptions to that generalization, but 95% of the time
that will be the cause.

HTH,