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