Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Creates table instead of exporting data
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 -- JT |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Exporting data from a table to blogs | New Users to Excel | |||
Exporting data to Excel pivot table from Access | Excel Discussion (Misc queries) | |||
Pivot table creates blank table | Charts and Charting in Excel | |||
Exporting data (Data Table) to Excel | Excel Worksheet Functions | |||
exporting summary data for pivot table! | Excel Worksheet Functions |