Creates table instead of exporting to Excel issue

[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

 

[Dirk Goldgar]

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

Did you define the constant acExport anywhere in the Excel context?

 

[JT]

Dirk..........Thanks for the reply to my question below. Can you explain
your answer a little more? I haven't defined the constant and am not sure
how you would define it. Thanks for the help.....

 

[Dirk Goldgar]

Dirk..........Thanks for the reply to my question below. Can you
explain your answer a little more? I haven't defined the constant
and am not sure how you would define it. Thanks for the help.....

As I understand it -- and tell me if I'm wrong -- the code you posted is
running in Excel. The constant acExport is defined in the Microsoft
Access library, so if you don't have a reference to that library, it
will not be defined, and will be treated as an undefined variable. If
you have the VB Editor set up to require variable declarations, the code
won't compile; since the code appears to be compiling, I guess you
don't have that option checked. In that case, "acExport" will be
automatically defined as a variable of type Variant, with an initial
value of {Empty} (which is a special value that is given to
uninitialized variants).

In Access, the constant acExport is defined as having a value of 1.
That's not the value you'll get if you try to use an uninitialized,
empty variant in your call to TransferSpreadsheet. I'm a little
surprised that the call even works that way, but if it does, I'll bet
that the empty argument is interpreted as the default, acImport.

So, in your code, either change the call to TransferSpreadsheet to this
(replacing the named constant with a literal):

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

.... or else add a line to the code defining the constant acExport, so
you can leave your TransferSpreadsheet call unchanged:

Const acExport = 1

' ...

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

If I'm write about what's going on, either of those changes should fix
the problem.

 

[JT]

Dirk.........thanks for your help. that makes a lot more sense to me now. I
added a line of code and defined the export as 1 and it is working like I
want it to. Thanks again for your help and quick response.