"JT" <(E-Mail Removed)> wrote in message
news:CA4BF6A4-0608-454A-A369-(E-Mail Removed)
> 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.....
>
>> "JT" <(E-Mail Removed)> wrote in message
>> news:49E5C5C0-A419-4ACF-B9CC-(E-Mail Removed)
>>> 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?
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.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
Similar Threads
