Access: Call SubB() from SubA() can't get Var from SubA() into Sub

  • Thread starter Thread starter EagleOne@microsoftdiscussiongroups
  • Start date Start date
E

EagleOne@microsoftdiscussiongroups

2003

This s/b simple but:

In Access, I call SubB() from SubA(). I want to use a variable in SubB()
created in SubB(). SubB() is not passed the variable.

What obvious am I not doing?

TIA EagleOne
 
OK, I used:

Public myFileName As String (placed prior to SubA() or SubB())

In SubA(),
Dim myFileName as String
...
...
myFileName = "Anything"
DoCmd.TransferSpreadsheet acExport, cSpreadsheetTypeExcel8, _
"myTable", myPath & myFileName, True
SubB()
End Sub

SubB(), 'This Dev Anish's procedure
...
...
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

With objXL.Application
.Visible = True
'Open the Workbook
.workbooks.Open myFileName
End WithEnd Sub

Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb
Dim wks As Worksheet 'Code fails here!!!!!!!!!!!!!!!!!!!
Dim myRange As Range
Dim myRange2 As Range
Dim myCell As Range
Set wks = Nothing

The Code fails at Dim wks As Worksheet!!!
I noted that Excel is (was) Open and no workbook is active
Also noted that the Variable myFileName was empty!!!!
Therefore, .workbooks.Open myFileName could not have worked.

Note the DoCmd in SubA()
Do I have to close the file immediately after the DoCmd? Then re-open it in
SubB()?

TIA EagleOne
 
Unless you have a reference set to Excel (through Tools | References), you
cannot declare variables As Worksheet or As Range. Elsewhere, you're using
Late Binding. Just change the four declarations to

Dim wks As Object
Dim myRange As Object
Dim myRange2 As Object
Dim myCell As Object

The reason myFileName is empty in SubB is because you've declared myFileName
as a variable inside of SubA, which means that SubA refers to that local
variable rather than the public one you've also declared with the same name.
This is one of the reasons why most of us use a naming convention:
module-level variables are declared as mstrFileName, global variables are
declared as gstrFileName.

Why not pass the filename though? It's seldom necessary (nor that good an
idea) to rely on public variables. For one thing, they get reset if an error
occurs. And if you're not careful, other routines could be changing the
value of the variable as well, so you won't be sure what value it contains.

Note, btw, that you're exporting to myPath & myFileName in SubA. That means
you should be opening myPath & myFileName in SubB, not just myFileName.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
 
Doug, THANKS!

How do I code: "Why not pass the filename though?"
 
Doug how do I code:

Module level variables as declared as mstrFileName,
global variables are declared as gstrFileName. Sounds like a great idea.

My newness shows!!!!
 
Change:

SubB()

to

Call SubB(myPath & myFileName)

Change:

SubB()

to

SubB(myFileName As String)


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
 
It's just a name.

Previously, you had

Public myFileName As String

placed prior to SubA() or SubB()

and

Dim myFileName as String

inside SubA().

Since the first declaration was intended to be a module-level variable,
you'd use a different name for it:

Public mstrFileName As String

Take a look at some of the resources related to Naming Conventions that Jeff
Conrad lists at
http://www.accessmvp.com/JConrad/accessjunkie/resources.html#NamingConventions




--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
 
Doug, myPathFile string is still empty (not passed).

Below is the actual code that I am using (stripped for duplicative info)

When the procedure gets into XL terms it begins to fail.

I am assuming that because myPathFile is empty, then Excel does not open
which in turn means that Excel's libraries are not available, therefore the
code fails.

I realize that I am doing something wrong, but I am too close to see it.

Thanks so much for the help.

EagleOne

' Next is an Access VBA procedure
Sub Export_Files_Macro()
Dim myPath As String
Dim myDate As String
Dim myTime As String
Dim myFileName As String
Dim myPathFile As String

myPath = CurrentProject.Path & "\"
myDate = Replace(Date, "/", "-")
myTime = IIf(Len(Hour(Time())) = 1, "0" & Hour(Time()), Hour(Time())) &
Minute(Time())
myFileName = "CHR_ALL_AAASITE_TBL " & myDate & " " & myTime & ".xls"
myPathFile = myPath & myFileName
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8,
"CHR_ALL_AAASITE_TBL", myPath & myFileName, True
Call FormatXLSheets(myPathFile)
...
...
...
...
...
...
...
End Sub

Sub FormatXLSheets(myPathFile As String)

'
'Code Courtesy of Dev Ashish
'
Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim objActiveWkb As Object

' Related to Excel procedures below
Dim wks As Object
Dim myRange As Object
Dim myRange2 As Object
Dim myCell As Object
Dim worksheets As Object
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long
Dim MaxRows As Long
Dim MaxColumns As Long
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long

If fIsAppRunning("Excel") Then
Set objXL = GetObject(, "Excel.Application")
boolXL = False
Else
Set objXL = CreateObject("Excel.Application")
boolXL = True
End If

With objXL.Application
.Visible = True
'Open the Workbook
.workbooks.Open myPathFile
End With

Set objActiveWkb = objXL.Application.ActiveWorkBook

With objActiveWkb

Set wks = Nothing
For Each wks In worksheets
With wks
MaxRows = .Rows.Count
MaxColumns = .Columns.Count
End With

myRowsToProcess = Cells.Find(What:="*", After:=wks.Cells(1, 1),
LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious,
SearchOrder:=xlByRows).Row
myColumnsToProcess = Cells.Find(What:="*", After:=wks.Cells(1,
1), LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchDirection:=xlPrevious,
SearchOrder:=xlByColumns).Column
myRowsToProcess = IIf(myRowsToProcess > MaxRows, MaxRows,
myRowsToProcess)
myColumnsToProcess = IIf(myColumnsToProcess > MaxColumns,
MaxColumns, myColumnsToProcess)
Set myRange = wks.Range(Cells(1, 1), Cells(myRowsToProcess,
myColumnsToProcess))
Set myRange = Intersect(myRange,
myRange.SpecialCells(xlCellTypeVisible))
myRange.AutoFilter
With wks
'...
'...
'... Various XL sheet Formating
'...
'...
'...
'...
...
End With
Next wks
End With
End Sub
 
In FormatXLSheets, remove the declaration

Dim Worksheets As Object

and change

For Each wks In worksheets

to

For Each wks In .Worksheets

You were missing the period in front of Worksheets (since it's a Collection
belonging to objActiveWkb)



--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


"EagleOne@microsoftdiscussiongroups"
 
Doug,

I am so embarassed. The myPathFile is not being Passed to FormatXLSheets
(myPathFile as String).

myPathFile is "loaded" in the previous procedure. It is not being passed.

I used the same logic for two test subs and it works fine as you stated -
but not above. Looking for anything ......

Therefore:
The declarations below are placed in my current VBA module near the
top-of-the-file. They apply to: Dev Ashish's fIsAppRunning("Excel")
function.

Should they be in the W/S (or another Access module) module?



Private Declare Function apiFindWindow Lib "user32" Alias _
"FindWindowA" (ByVal strClass As String, _
ByVal lpWindow As String) As Long

Private Declare Function apiSendMessage Lib "user32" Alias _
"SendMessageA" (ByVal Hwnd As Long, ByVal Msg As Long, ByVal _
wParam As Long, lParam As Long) As Long

Private Declare Function apiSetForegroundWindow Lib "user32" Alias _
"SetForegroundWindow" (ByVal Hwnd As Long) As Long

Private Declare Function apiShowWindow Lib "user32" Alias _
"ShowWindow" (ByVal Hwnd As Long, ByVal nCmdShow As Long) As Long

Private Declare Function apiIsIconic Lib "user32" Alias _
"IsIconic" (ByVal Hwnd As Long) As Long
 
The reference to Excel 11 was made and the variable is now being passed. I
did miss your earlier point about making a reference to the Excel 11 library.
I thought that I did not need the reference because I was passing the
variable via the procedures in the same module.

Now, I have a "cant apply a border" which I believe I will run down.

Thanks so much!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Back
Top