Creates table instead of exporting data

J

JT

I have a macro that in Excel that I would like to do some things in Access
and then export the data to an Excel file. Following is the code I am
working with. The issue is the macro is trying to create a table in Access
instead of Excel. I have indicated the line of code where the issue occurs.
Any suggestions or help on how to resolve this issue would be appreciated.
Thanks.....

Dim QueryName As String
Set AccApp = GetObject(, "Access.Application")

QueryName = "Clear DeptID Data Table"
AccApp.DoCmd.OpenQuery QueryName

QueryName = "Create DeptID Reference"
AccApp.DoCmd.OpenQuery QueryName

QueryName = "Update US Detail Table"
AccApp.DoCmd.OpenQuery QueryName

** Executes Excel Code here **

Dim qry As QueryDef
Dim strGp As String
Dim rst As Recordset

Do Until Len(Cells(r, 1)) = 0

strGp = Cells(r, 1)

On Error Resume Next
AccApp.CurrentDb.QueryDefs.Delete ("Group" & strGp & " Detail file")
On Error GoTo 0
Set qry = AccApp.CurrentDb.CreateQueryDef("Group" & strGp & " Detail file")

qry.Sql = "SELECT Detail_US.* FROM Detail_US WHERE (((Detail_US.Region)= """
& strGp & """));"

Set rst = AccApp.CurrentDb.OpenRecordset(qry.Name)

If rst.RecordCount <> 0 Then
TargetFile = "C:\Detail.xls"

** the next line of code tries to create a table in access instead of
exporting it to Excel**

AccApp.DoCmd.TransferSpreadsheet acExport, 8, "Group" & strGp & " Detail
file", TargetFile

End If

AccApp.CurrentDb.QueryDefs.Delete ("Group" & strGp & " Detail file")

r = r + 1

Loop
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top