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