VBA Code to export query data from MS Access to Excel

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

shams100 - 22 Nov 2005 19:43 GMT

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,

Reply to this Message

Ken Snell [MVP] - 22 Nov 2005 19:54 GMT

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

Signature

Ken Snell
<MS ACCESS MVP>

> I'm looking for an example VBA Code to export or transfer query data
> from MS Access
[quoted text clipped - 32 lines]
> this.
> Thanks,

Reply to this Message

shams100 - 22 Nov 2005 20:04 GMT

Ms 2003
sp1

Reply to this Message

Ken Snell [MVP] - 22 Nov 2005 23:30 GMT

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.

Signature

Ken Snell
<MS ACCESS MVP>

> Ms 2003
> sp1

Reply to this Message

George Nicholson - 23 Nov 2005 01:55 GMT

> 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,

Signature

George Nicholson

Remove 'Junk' from return address.

> I'm looking for an example VBA Code to export or transfer query data
> from MS Access
[quoted text clipped - 32 lines]
> this.
> Thanks,

Reply to this Message