Need Help passing a variable from a form action to a module

[careich]

I am trying to pass a variable 'headrows' from the on event action from a form to a public module that imports spreadsheeet data from excel. This number changes depending on which spreadsheet is being imported so there is a seperate inport button for each type of spreadsheet.

Here is the basics of what I have tried and I know someone will make me feel real stupid!
--This is from the button on the form --
Private Sub cmdImport_Click()
On Error Resume Next
Dim strMsg As String
Dim strFile As String
Dim xcomp As String
xcomp = " "
Dim strFilter As String
Dim strInputFileName As String
Dim headrows As Integer
headrows = 1
-----
Then in the module

' Set this variable to the first row that contains actual data.
intStartRow = headrows
----
Thanks,
CR

 

[pbaldy]

Since you've declared the variable inside that form procedure, it only has scope there. Declare it at the top of a standard module. You could also change your function to accept it as a parameter instead.

 

[careich]

I guess I don't understand the public variable piece.

I don't care how I set the variable 'intstartrow' the easier the better. It will be either 1 2 or 3 depending on which spreadsheet is being imported. since each spreadsheet has its own import button I think this is the place to set it but I cant get the module to use the number.

Here is all the beginning code to where the call is made in the module.

And more of the button action code if you can show me were to dim the variable and how to call it in the module it would be great.

As you can tell by the question I am a novice just trying to fix this!

----module code----
Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String) As String
On Error GoTo ProcessFileImport_Error

' Excel object variables

Dim appexcel As Excel.Application
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet

' Access object variables
Dim dbs As DAO.Database
Dim rstRead As DAO.Recordset
Dim rstWrite As DAO.Recordset
Dim fld As DAO.Field

' Declared variables
Dim bytWks As Byte
Dim bytMaxPages As Byte
Dim intStartRow As Integer
Dim strData As String
Dim intMaxRow As Integer
Dim strSQL As String
Dim strMsg As String
Dim intLastCol As Integer
Dim intRow As Integer
Dim intRec As Integer
Dim strCurrFld As String
Dim intCol As Integer
Dim intLen As Integer
Dim varValue As Variant
Dim lngErrs As Long

Const cPassword As String = "xxx999"

DoCmd.Hourglass True

' Create the Excel Applicaiton, Workbook and Worksheet and Database object
Set appexcel = Excel.Application
Set wbk = appexcel.Workbooks.Open(sFile)
Set dbs = CurrentDb

' Optionally, you can protect / unprotect with a password
'wkb.Unprotect (cPassword)

' You could loop through sheets, but for this example, we'll just do one.
bytMaxPages = 1

' Sometimes there is header info, so the "Start Row" isn't the first one.
' Set this variable to the first row that contains actual data.
intStartRow = "this is where i need the number from the import button"

--------

Private Sub cmdImport_Click()
On Error Resume Next
Dim strMsg As String
Dim strFile As String
Dim xcomp As String
xcomp = " "
Dim strFilter As String
Dim strInputFileName As String

"Here is where i was trying to set the value"

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)
Me.txtFile = strInputFileName
strFile = Nz(Me.txtFile, "")
------
CR

 

[pbaldy]

Personally I would probably change to this:

Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String, ByVal intStartRow As Integer) As String

Take it out of your declared variables in the function, and just pass the value when you call the function. To do it the way you're doing it, you'd take the declaration out of your procedure and put it at the top of the standard module containing the function:

Option Compare Database
Option Explicit

Public headrows As Integer

 

[careich]

OK I replaced the line in the module with

Public Function ProcessFileImport(ByVal sFile As String, ByVal sTable As String, ByVal intStartRow As Integer) As String

How do I pass the number of rows back from the command button
I tried

inStartRow = 1

I get a variable not defined error.

I appreciate your help!!

 

[pbaldy]

Where you call the function, instead of

ProcessFileImport "blah", "moreblah"

you'd have

ProcessFileImport "blah", "moreblah", 1

 

[careich]

Worked like a charm! Thanks
I spent several hours on a 3 minute fix!!!

 

[pbaldy]

No problemo, and welcome to the site by the way!

 

[mikerea90]

I am also using this formula and I have a quick question. After I run it, the table is loaded, but comes up empty... does anyone know why this would occur?? Thank you.