Perfect. Thanks!


"DM Unseen" wrote in message
ups.com...
Guys

Global variables are locked to their host application. So Excel can
never use VBA vairaibles from Access and vice versa. (COM) Automation
can only pass on information through procedure parameters.

So maybe do someting that uses a parameter to pass the filename from Xl
to Access.

example

Sub runaccess()
Set appAccess = CreateObject("Access.Applicati*on")
appAccess.OpenCurrentDatabase "C:\Projects\Payroll.mdb", False
'appAccess.Visible = False


appAccess.Run "ImportExport" , FileNme


Set appAccess = Nothing
End Sub


In Access it is running this code; Notice FileNme is what I want to
pass...


Sub ImportExport(strFilename as string )


Dim strFilter As String
Dim strInputFileName As String


DoCmd.SetWarnings False
DoCmd.OpenQuery "del_initial"


DoCmd.TransferText acImportFixed, "FGRODTA Import Specification", _
"FGRODTA_Initial", strFilename


DoCmd.TransferText acExportDelim, "FGRODTA_Initial Export
Specification", _
"FGRODTA_Initial", "C:\Projects\FGRODTA_Tab.txt"


DoCmd.OpenQuery "del_initial"


DoCmd.SetWarnings True


End Sub


BWT Excel can import and Export and convert text files as well, as long
it keeps within excel row limits. It is slightly lesss sophisticated as
Access though, esp on the older versions of XL.


DM Unseen