Passing variable to Access
 

Anyone know how to pass a variable from Excel to Access? From Excel I'm
running a bit of code that requires the import of a file. I want to pass
the filename to Access from the Excel Module.

Cheers,

Job

Passing variable to Access
 

try to declare your variable as a Public variable in declarations area as
follows

Public var1 as string
Public var2 as integer

sub x ()
blablalbla
end sub

etc...

Passing variable to Access
 

That is what I currently have and I've tried Global as well, but once I open
and start the code in Access, the variable is blank.

Here is a portion of the code maybe this will help explain; FileNme is the
variable I want to pass


Sub eachfile()

Dim i, objFSO, file, folder

Set objFSO = CreateObject("Scripting.FileSystemObject")

Set folder = objFSO.GetFolder("C:\Projects\")

For Each file In folder.Files
Debug.Print file.Name

If Left(file.Name, 8) = "FYRODTA_" Then

FileNme = folder & "\" & file.Name

runaccess

End If
Next

End Sub


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

appAccess.Run "ImportExport"

Set appAccess = Nothing
End Sub


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

Sub ImportExport()

Dim strFilter As String
Dim strInputFileName As String

DoCmd.SetWarnings False
DoCmd.OpenQuery "del_initial"

DoCmd.TransferText acImportFixed, "FGRODTA Import Specification", _
"FGRODTA_Initial", FileNme 'strInputFileName '"C:\Projects\FGRODTA.txt"

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

DoCmd.OpenQuery "del_initial"

DoCmd.SetWarnings True

End Sub




"Snake Plissken" wrote in message
...
try to declare your variable as a Public variable in declarations area as
follows

Public var1 as string
Public var2 as integer

sub x ()
blablalbla
end sub

etc...

Passing variable to Access
 

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

Passing variable to Access
 

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