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
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
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...
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" <[email protected]> wrote in message
news:[email protected]...
> 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...
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 =3D CreateObject("Access.Applicati=ADon")
appAccess.OpenCurrentDatabase "C:\Projects\Payroll.mdb", False
'appAccess.Visible =3D False
appAccess.Run "ImportExport" , FileNme
Set appAccess =3D 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
Perfect. Thanks!
"DM Unseen" <[email protected]> wrote in message
news:[email protected]...
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
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks