2003

2003 up to date


If I use Access Automation to export Access data to Excel files, there is
always one last instance of Excel in memory unti I close Access.

Normally not a problem, unless I wish to do another export.

What then occurs is that Access Automation cannot open any Excel files
attached to that instance of Excel. In addition, Access VBA procedures which
veryify Error status do not run.

In fact, if I start the Access Automation macro without previously closing
all instances of Excel, fail in the same manner as above.

In short, to be successful with Access to Excel Automation, I must first
close all Excel files and Excel itself. Also, if certain procedures in the
Automation macros did not complete (do to error status check), I must close
and restart Access.

Thus, both Access must be "rebooted" and Excel must be created 1st time in
that session.

BTW I am using the Dev Ashish code somewhat tailored as below:

Sub FormatXLSheets(myPathFile As String, myFileName As String, _
myPath As String, myDate As String, myTime As String)
'
'Basic Code Courtesy of Dev Ashish
'

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim MainSiteName As String
Dim objActiveWkb As Object
Dim Wks As Object
Dim FullSheetRng As Object
Dim OneColFilteredRng As Object
Dim FullSheetFilteredRng As Object
Dim ResizedRngToCopy As Object
Dim myCell4OffHold As Object
Dim myCell4Offset As Object
Dim TempShtRng As Object
Dim ResizedRngToDelete As Object
Dim myCell As Object
Dim ThisWorkbook As Object
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long
Dim MaxRows As Long
Dim MaxColumns As Long
'Following necessary for Sort sequences in Excel/Access 2003
Dim xlSortOnValues As Long
Dim xlAscending As Long
Dim xlSortNormal As Long
xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0


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
.Visible = False
'Open the Workbook
.Workbooks.Open myPathFile
'.ActiveWorkBook.RunAutoMacros xlAutoOpen
End With
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb
Set Wks = Nothing
For Each Wks In .Worksheets

Wks.Activate

'
'
'
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'
'
'

Sheets("Temp").Paste
objXL.CutCopyMode = False

objXL.DisplayAlerts = False

objXL.DisplayAlerts = True


Next wks

objActiveWkb.Close savechanges:=True

objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing

'Next section crude attempt to blow away extra Excel sessions

If fIsAppRunning("Excel") Then
Do While fIsAppRunning("Excel")
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
On Error Resume Next
Set objActiveWkb = objXL.Application.ActiveWorkbook
Err.Clear
If Not objActiveWkb Is Nothing Then
objXL.Application.Visible = True
Do While Not objActiveWkb Is Nothing
On Error Resume Next
Set objActiveWkb = objXL.Application.ActiveWorkbook
On Error Resume Next
objActiveWkb.Close savechanges:=True
If objActiveWkb Is Nothing Then
objXL.Application.Visible = False
Exit Do
End If
Loop
End If
objXL.Application.Quit
If objActiveWkb Is Nothing Then
Set objActiveWkb = Nothing: Set objXL = Nothing
Exit Do
End If
Loop
End If

End Sub


Any suggestions as correct the challenge?

TIA EagleOne

EagleOne,
here is part of the code I use successfully to format files in excel.
Below is the opening of excel and closing of excel part of the code.

In your code, you first test to see if excel is already running.
In your closing part of the code you also can check to see if you opened a
new instance of excel.
If you didn't need to open a new instance of excel, you don't need to close
it.

'opening excel here
If fIsAppRunning("excel", False) Then 'yes it is running
' Get a reference to currently running Excel window
Set objXLApp = GetObject(, "Excel.Application")
blnExcelExists = True
Else
' Excel is not currently running so create a new instance
Set objXLApp = CreateObject("Excel.Application")
End If


'closing excel here
'close the instance of Excel created by code
If Not blnExcelExists Then
objXLApp.Quit
End If

If Not objActiveWkb Is Nothing Then
Set objActiveWkb = Nothing
End If
If Not objXLApp Is Nothing Then
Set objXLApp = Nothing
End If
If Not db Is Nothing Then
Set db = Nothing
End If



Jeanette Cunningham -- Melbourne Victoria Australia


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 2003
>
> 2003 up to date
>
>
> If I use Access Automation to export Access data to Excel files, there is
> always one last instance of Excel in memory unti I close Access.
>
> Normally not a problem, unless I wish to do another export.
>
> What then occurs is that Access Automation cannot open any Excel files
> attached to that instance of Excel. In addition, Access VBA procedures
> which
> veryify Error status do not run.
>
> In fact, if I start the Access Automation macro without previously closing
> all instances of Excel, fail in the same manner as above.
>
> In short, to be successful with Access to Excel Automation, I must first
> close all Excel files and Excel itself. Also, if certain procedures in
> the
> Automation macros did not complete (do to error status check), I must
> close
> and restart Access.
>
> Thus, both Access must be "rebooted" and Excel must be created 1st time in
> that session.
>
> BTW I am using the Dev Ashish code somewhat tailored as below:
>
> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
> myPath As String, myDate As String, myTime As String)
> '
> 'Basic Code Courtesy of Dev Ashish
> '
>
> Dim objXL As Object
> Dim strWhat As String, boolXL As Boolean
> Dim MainSiteName As String
> Dim objActiveWkb As Object
> Dim Wks As Object
> Dim FullSheetRng As Object
> Dim OneColFilteredRng As Object
> Dim FullSheetFilteredRng As Object
> Dim ResizedRngToCopy As Object
> Dim myCell4OffHold As Object
> Dim myCell4Offset As Object
> Dim TempShtRng As Object
> Dim ResizedRngToDelete As Object
> Dim myCell As Object
> Dim ThisWorkbook As Object
> Dim myRowsToProcess As Long
> Dim myColumnsToProcess As Long
> Dim MaxRows As Long
> Dim MaxColumns As Long
> 'Following necessary for Sort sequences in Excel/Access 2003
> Dim xlSortOnValues As Long
> Dim xlAscending As Long
> Dim xlSortNormal As Long
> xlSortOnValues = 0
> xlAscending = 1
> xlSortNormal = 0
>
>
> 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
> .Visible = False
> 'Open the Workbook
> .Workbooks.Open myPathFile
> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
> End With
> Set objActiveWkb = objXL.Application.ActiveWorkbook
>
> With objActiveWkb
> Set Wks = Nothing
> For Each Wks In .Worksheets
>
> Wks.Activate
>
> '
> '
> '
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> '
> '
> '
>
> Sheets("Temp").Paste
> objXL.CutCopyMode = False
>
> objXL.DisplayAlerts = False
>
> objXL.DisplayAlerts = True
>
>
> Next wks
>
> objActiveWkb.Close savechanges:=True
>
> objXL.Application.Quit
> Set objActiveWkb = Nothing: Set objXL = Nothing
>
> 'Next section crude attempt to blow away extra Excel sessions
>
> If fIsAppRunning("Excel") Then
> Do While fIsAppRunning("Excel")
> On Error Resume Next
> Set objXL = GetObject(, "Excel.Application")
> On Error Resume Next
> Set objActiveWkb = objXL.Application.ActiveWorkbook
> Err.Clear
> If Not objActiveWkb Is Nothing Then
> objXL.Application.Visible = True
> Do While Not objActiveWkb Is Nothing
> On Error Resume Next
> Set objActiveWkb = objXL.Application.ActiveWorkbook
> On Error Resume Next
> objActiveWkb.Close savechanges:=True
> If objActiveWkb Is Nothing Then
> objXL.Application.Visible = False
> Exit Do
> End If
> Loop
> End If
> objXL.Application.Quit
> If objActiveWkb Is Nothing Then
> Set objActiveWkb = Nothing: Set objXL = Nothing
> Exit Do
> End If
> Loop
> End If
>
> End Sub
>
>
> Any suggestions as correct the challenge?
>
> TIA EagleOne

Thank you Jeannette,

I wish I knew why, the Access MVP's avoided this challenge for the last two
days. One actually started to - but dropped the ball. A challenge like this
would have been answered 5 times in the Excel newsgroup.

Again thanks!


"Jeanette Cunningham" wrote:

> EagleOne,
> here is part of the code I use successfully to format files in excel.
> Below is the opening of excel and closing of excel part of the code.
>
> In your code, you first test to see if excel is already running.
> In your closing part of the code you also can check to see if you opened a
> new instance of excel.
> If you didn't need to open a new instance of excel, you don't need to close
> it.
>
> 'opening excel here
> If fIsAppRunning("excel", False) Then 'yes it is running
> ' Get a reference to currently running Excel window
> Set objXLApp = GetObject(, "Excel.Application")
> blnExcelExists = True
> Else
> ' Excel is not currently running so create a new instance
> Set objXLApp = CreateObject("Excel.Application")
> End If
>
>
> 'closing excel here
> 'close the instance of Excel created by code
> If Not blnExcelExists Then
> objXLApp.Quit
> End If
>
> If Not objActiveWkb Is Nothing Then
> Set objActiveWkb = Nothing
> End If
> If Not objXLApp Is Nothing Then
> Set objXLApp = Nothing
> End If
> If Not db Is Nothing Then
> Set db = Nothing
> End If
>
>
>
> Jeanette Cunningham -- Melbourne Victoria Australia
>
>
> <(E-Mail Removed)> wrote in message
> news:(E-Mail Removed)...
> > 2003
> >
> > 2003 up to date
> >
> >
> > If I use Access Automation to export Access data to Excel files, there is
> > always one last instance of Excel in memory unti I close Access.
> >
> > Normally not a problem, unless I wish to do another export.
> >
> > What then occurs is that Access Automation cannot open any Excel files
> > attached to that instance of Excel. In addition, Access VBA procedures
> > which
> > veryify Error status do not run.
> >
> > In fact, if I start the Access Automation macro without previously closing
> > all instances of Excel, fail in the same manner as above.
> >
> > In short, to be successful with Access to Excel Automation, I must first
> > close all Excel files and Excel itself. Also, if certain procedures in
> > the
> > Automation macros did not complete (do to error status check), I must
> > close
> > and restart Access.
> >
> > Thus, both Access must be "rebooted" and Excel must be created 1st time in
> > that session.
> >
> > BTW I am using the Dev Ashish code somewhat tailored as below:
> >
> > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
> > myPath As String, myDate As String, myTime As String)
> > '
> > 'Basic Code Courtesy of Dev Ashish
> > '
> >
> > Dim objXL As Object
> > Dim strWhat As String, boolXL As Boolean
> > Dim MainSiteName As String
> > Dim objActiveWkb As Object
> > Dim Wks As Object
> > Dim FullSheetRng As Object
> > Dim OneColFilteredRng As Object
> > Dim FullSheetFilteredRng As Object
> > Dim ResizedRngToCopy As Object
> > Dim myCell4OffHold As Object
> > Dim myCell4Offset As Object
> > Dim TempShtRng As Object
> > Dim ResizedRngToDelete As Object
> > Dim myCell As Object
> > Dim ThisWorkbook As Object
> > Dim myRowsToProcess As Long
> > Dim myColumnsToProcess As Long
> > Dim MaxRows As Long
> > Dim MaxColumns As Long
> > 'Following necessary for Sort sequences in Excel/Access 2003
> > Dim xlSortOnValues As Long
> > Dim xlAscending As Long
> > Dim xlSortNormal As Long
> > xlSortOnValues = 0
> > xlAscending = 1
> > xlSortNormal = 0
> >
> >
> > 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
> > .Visible = False
> > 'Open the Workbook
> > .Workbooks.Open myPathFile
> > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
> > End With
> > Set objActiveWkb = objXL.Application.ActiveWorkbook
> >
> > With objActiveWkb
> > Set Wks = Nothing
> > For Each Wks In .Worksheets
> >
> > Wks.Activate
> >
> > '
> > '
> > '
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > 'Many VBA lines of w/s formating
> > '
> > '
> > '
> >
> > Sheets("Temp").Paste
> > objXL.CutCopyMode = False
> >
> > objXL.DisplayAlerts = False
> >
> > objXL.DisplayAlerts = True
> >
> >
> > Next wks
> >
> > objActiveWkb.Close savechanges:=True
> >
> > objXL.Application.Quit
> > Set objActiveWkb = Nothing: Set objXL = Nothing
> >
> > 'Next section crude attempt to blow away extra Excel sessions
> >
> > If fIsAppRunning("Excel") Then
> > Do While fIsAppRunning("Excel")
> > On Error Resume Next
> > Set objXL = GetObject(, "Excel.Application")
> > On Error Resume Next
> > Set objActiveWkb = objXL.Application.ActiveWorkbook
> > Err.Clear
> > If Not objActiveWkb Is Nothing Then
> > objXL.Application.Visible = True
> > Do While Not objActiveWkb Is Nothing
> > On Error Resume Next
> > Set objActiveWkb = objXL.Application.ActiveWorkbook
> > On Error Resume Next
> > objActiveWkb.Close savechanges:=True
> > If objActiveWkb Is Nothing Then
> > objXL.Application.Visible = False
> > Exit Do
> > End If
> > Loop
> > End If
> > objXL.Application.Quit
> > If objActiveWkb Is Nothing Then
> > Set objActiveWkb = Nothing: Set objXL = Nothing
> > Exit Do
> > End If
> > Loop
> > End If
> >
> > End Sub
> >
> >
> > Any suggestions as correct the challenge?
> >
> > TIA EagleOne
>
>
>

Glad I could help.
When we volunteers answer questions, it is easier to pick something that we
either know off by heart, or something that we are working on at the moment
and are very familiar with. Many of us also have limited time, so just do a
couple of questions. Many MVP's also answer questions on the other access
forums around.


Jeanette Cunningham -- Melbourne Victoria Australia

"EagleOne@microsoftdiscussiongroups"
<(E-Mail Removed)> wrote in
message news:7C3C7731-7DE5-48A7-BD4F-(E-Mail Removed)...
> Thank you Jeannette,
>
> I wish I knew why, the Access MVP's avoided this challenge for the last
> two
> days. One actually started to - but dropped the ball. A challenge like
> this
> would have been answered 5 times in the Excel newsgroup.
>
> Again thanks!
>
>
> "Jeanette Cunningham" wrote:
>
>> EagleOne,
>> here is part of the code I use successfully to format files in excel.
>> Below is the opening of excel and closing of excel part of the code.
>>
>> In your code, you first test to see if excel is already running.
>> In your closing part of the code you also can check to see if you opened
>> a
>> new instance of excel.
>> If you didn't need to open a new instance of excel, you don't need to
>> close
>> it.
>>
>> 'opening excel here
>> If fIsAppRunning("excel", False) Then 'yes it is running
>> ' Get a reference to currently running Excel window
>> Set objXLApp = GetObject(, "Excel.Application")
>> blnExcelExists = True
>> Else
>> ' Excel is not currently running so create a new instance
>> Set objXLApp = CreateObject("Excel.Application")
>> End If
>>
>>
>> 'closing excel here
>> 'close the instance of Excel created by code
>> If Not blnExcelExists Then
>> objXLApp.Quit
>> End If
>>
>> If Not objActiveWkb Is Nothing Then
>> Set objActiveWkb = Nothing
>> End If
>> If Not objXLApp Is Nothing Then
>> Set objXLApp = Nothing
>> End If
>> If Not db Is Nothing Then
>> Set db = Nothing
>> End If
>>
>>
>>
>> Jeanette Cunningham -- Melbourne Victoria Australia
>>
>>
>> <(E-Mail Removed)> wrote in message
>> news:(E-Mail Removed)...
>> > 2003
>> >
>> > 2003 up to date
>> >
>> >
>> > If I use Access Automation to export Access data to Excel files, there
>> > is
>> > always one last instance of Excel in memory unti I close Access.
>> >
>> > Normally not a problem, unless I wish to do another export.
>> >
>> > What then occurs is that Access Automation cannot open any Excel files
>> > attached to that instance of Excel. In addition, Access VBA procedures
>> > which
>> > veryify Error status do not run.
>> >
>> > In fact, if I start the Access Automation macro without previously
>> > closing
>> > all instances of Excel, fail in the same manner as above.
>> >
>> > In short, to be successful with Access to Excel Automation, I must
>> > first
>> > close all Excel files and Excel itself. Also, if certain procedures in
>> > the
>> > Automation macros did not complete (do to error status check), I must
>> > close
>> > and restart Access.
>> >
>> > Thus, both Access must be "rebooted" and Excel must be created 1st time
>> > in
>> > that session.
>> >
>> > BTW I am using the Dev Ashish code somewhat tailored as below:
>> >
>> > Sub FormatXLSheets(myPathFile As String, myFileName As String, _
>> > myPath As String, myDate As String, myTime As String)
>> > '
>> > 'Basic Code Courtesy of Dev Ashish
>> > '
>> >
>> > Dim objXL As Object
>> > Dim strWhat As String, boolXL As Boolean
>> > Dim MainSiteName As String
>> > Dim objActiveWkb As Object
>> > Dim Wks As Object
>> > Dim FullSheetRng As Object
>> > Dim OneColFilteredRng As Object
>> > Dim FullSheetFilteredRng As Object
>> > Dim ResizedRngToCopy As Object
>> > Dim myCell4OffHold As Object
>> > Dim myCell4Offset As Object
>> > Dim TempShtRng As Object
>> > Dim ResizedRngToDelete As Object
>> > Dim myCell As Object
>> > Dim ThisWorkbook As Object
>> > Dim myRowsToProcess As Long
>> > Dim myColumnsToProcess As Long
>> > Dim MaxRows As Long
>> > Dim MaxColumns As Long
>> > 'Following necessary for Sort sequences in Excel/Access 2003
>> > Dim xlSortOnValues As Long
>> > Dim xlAscending As Long
>> > Dim xlSortNormal As Long
>> > xlSortOnValues = 0
>> > xlAscending = 1
>> > xlSortNormal = 0
>> >
>> >
>> > 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
>> > .Visible = False
>> > 'Open the Workbook
>> > .Workbooks.Open myPathFile
>> > '.ActiveWorkBook.RunAutoMacros xlAutoOpen
>> > End With
>> > Set objActiveWkb = objXL.Application.ActiveWorkbook
>> >
>> > With objActiveWkb
>> > Set Wks = Nothing
>> > For Each Wks In .Worksheets
>> >
>> > Wks.Activate
>> >
>> > '
>> > '
>> > '
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > 'Many VBA lines of w/s formating
>> > '
>> > '
>> > '
>> >
>> > Sheets("Temp").Paste
>> > objXL.CutCopyMode = False
>> >
>> > objXL.DisplayAlerts = False
>> >
>> > objXL.DisplayAlerts = True
>> >
>> >
>> > Next wks
>> >
>> > objActiveWkb.Close savechanges:=True
>> >
>> > objXL.Application.Quit
>> > Set objActiveWkb = Nothing: Set objXL = Nothing
>> >
>> > 'Next section crude attempt to blow away extra Excel sessions
>> >
>> > If fIsAppRunning("Excel") Then
>> > Do While fIsAppRunning("Excel")
>> > On Error Resume Next
>> > Set objXL = GetObject(, "Excel.Application")
>> > On Error Resume Next
>> > Set objActiveWkb = objXL.Application.ActiveWorkbook
>> > Err.Clear
>> > If Not objActiveWkb Is Nothing Then
>> > objXL.Application.Visible = True
>> > Do While Not objActiveWkb Is Nothing
>> > On Error Resume Next
>> > Set objActiveWkb = objXL.Application.ActiveWorkbook
>> > On Error Resume Next
>> > objActiveWkb.Close savechanges:=True
>> > If objActiveWkb Is Nothing Then
>> > objXL.Application.Visible = False
>> > Exit Do
>> > End If
>> > Loop
>> > End If
>> > objXL.Application.Quit
>> > If objActiveWkb Is Nothing Then
>> > Set objActiveWkb = Nothing: Set objXL = Nothing
>> > Exit Do
>> > End If
>> > Loop
>> > End If
>> >
>> > End Sub
>> >
>> >
>> > Any suggestions as correct the challenge?
>> >
>> > TIA EagleOne
>>
>>
>>

Jeanette,

FYI, your approach/logic is definitely leaves less "open instances" of Excel.

That said, there is still one remaining "phantom" instance held open by Access.

The reason this is important, is if I run my Format Excel worksheets macro via Access
automation a 2nd time in the same instance of Access (where there is still one remaining "phantom"
instance held open by Access) the formatting fails because Excel is still holding onto the objects
or some other object-related issue.

The reason I say this is I can "see" the instance of Excel in Task Manager. If I do not close the
1st Access instance (at which point the "phantom" instance of Excel disappears from Task Manager)
and then re-open Access, the Excel formatting fails.

I believe that the Access programmers need to figure out how to provide end users a VBA command to
kill all instances of Excel opened via Access automation.

There is no doubt in my mind that this Access automation bug is exactly why the Access Automation
MVP's ignored this issue - direct or indirect pressure from MS to not give the bug legs.

EagleOne

"Jeanette Cunningham" <(E-Mail Removed)> wrote:

>EagleOne,
>here is part of the code I use successfully to format files in excel.
>Below is the opening of excel and closing of excel part of the code.
>
>In your code, you first test to see if excel is already running.
>In your closing part of the code you also can check to see if you opened a
>new instance of excel.
>If you didn't need to open a new instance of excel, you don't need to close
>it.
>
>'opening excel here
> If fIsAppRunning("excel", False) Then 'yes it is running
> ' Get a reference to currently running Excel window
> Set objXLApp = GetObject(, "Excel.Application")
> blnExcelExists = True
> Else
> ' Excel is not currently running so create a new instance
> Set objXLApp = CreateObject("Excel.Application")
> End If
>
>
>'closing excel here
> 'close the instance of Excel created by code
> If Not blnExcelExists Then
> objXLApp.Quit
> End If
>
> If Not objActiveWkb Is Nothing Then
> Set objActiveWkb = Nothing
> End If
> If Not objXLApp Is Nothing Then
> Set objXLApp = Nothing
> End If
> If Not db Is Nothing Then
> Set db = Nothing
> End If
>
>
>
>Jeanette Cunningham -- Melbourne Victoria Australia
>
>
><(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> 2003
>>
>> 2003 up to date
>>
>>
>> If I use Access Automation to export Access data to Excel files, there is
>> always one last instance of Excel in memory unti I close Access.
>>
>> Normally not a problem, unless I wish to do another export.
>>
>> What then occurs is that Access Automation cannot open any Excel files
>> attached to that instance of Excel. In addition, Access VBA procedures
>> which
>> veryify Error status do not run.
>>
>> In fact, if I start the Access Automation macro without previously closing
>> all instances of Excel, fail in the same manner as above.
>>
>> In short, to be successful with Access to Excel Automation, I must first
>> close all Excel files and Excel itself. Also, if certain procedures in
>> the
>> Automation macros did not complete (do to error status check), I must
>> close
>> and restart Access.
>>
>> Thus, both Access must be "rebooted" and Excel must be created 1st time in
>> that session.
>>
>> BTW I am using the Dev Ashish code somewhat tailored as below:
>>
>> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
>> myPath As String, myDate As String, myTime As String)
>> '
>> 'Basic Code Courtesy of Dev Ashish
>> '
>>
>> Dim objXL As Object
>> Dim strWhat As String, boolXL As Boolean
>> Dim MainSiteName As String
>> Dim objActiveWkb As Object
>> Dim Wks As Object
>> Dim FullSheetRng As Object
>> Dim OneColFilteredRng As Object
>> Dim FullSheetFilteredRng As Object
>> Dim ResizedRngToCopy As Object
>> Dim myCell4OffHold As Object
>> Dim myCell4Offset As Object
>> Dim TempShtRng As Object
>> Dim ResizedRngToDelete As Object
>> Dim myCell As Object
>> Dim ThisWorkbook As Object
>> Dim myRowsToProcess As Long
>> Dim myColumnsToProcess As Long
>> Dim MaxRows As Long
>> Dim MaxColumns As Long
>> 'Following necessary for Sort sequences in Excel/Access 2003
>> Dim xlSortOnValues As Long
>> Dim xlAscending As Long
>> Dim xlSortNormal As Long
>> xlSortOnValues = 0
>> xlAscending = 1
>> xlSortNormal = 0
>>
>>
>> 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
>> .Visible = False
>> 'Open the Workbook
>> .Workbooks.Open myPathFile
>> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
>> End With
>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>
>> With objActiveWkb
>> Set Wks = Nothing
>> For Each Wks In .Worksheets
>>
>> Wks.Activate
>>
>> '
>> '
>> '
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> 'Many VBA lines of w/s formating
>> '
>> '
>> '
>>
>> Sheets("Temp").Paste
>> objXL.CutCopyMode = False
>>
>> objXL.DisplayAlerts = False
>>
>> objXL.DisplayAlerts = True
>>
>>
>> Next wks
>>
>> objActiveWkb.Close savechanges:=True
>>
>> objXL.Application.Quit
>> Set objActiveWkb = Nothing: Set objXL = Nothing
>>
>> 'Next section crude attempt to blow away extra Excel sessions
>>
>> If fIsAppRunning("Excel") Then
>> Do While fIsAppRunning("Excel")
>> On Error Resume Next
>> Set objXL = GetObject(, "Excel.Application")
>> On Error Resume Next
>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>> Err.Clear
>> If Not objActiveWkb Is Nothing Then
>> objXL.Application.Visible = True
>> Do While Not objActiveWkb Is Nothing
>> On Error Resume Next
>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>> On Error Resume Next
>> objActiveWkb.Close savechanges:=True
>> If objActiveWkb Is Nothing Then
>> objXL.Application.Visible = False
>> Exit Do
>> End If
>> Loop
>> End If
>> objXL.Application.Quit
>> If objActiveWkb Is Nothing Then
>> Set objActiveWkb = Nothing: Set objXL = Nothing
>> Exit Do
>> End If
>> Loop
>> End If
>>
>> End Sub
>>
>>
>> Any suggestions as correct the challenge?
>>
>> TIA EagleOne
>

EagleOne,
I have seen this problem when using either OutputTo or TransferSpreadsheet
to export to excel then using automation to format the excel file.
If you are using the above process, finish the export completely before
starting any code that uses automation to format the exported file.

The other usual reason for this problem, is related to how you structure the
code that uses automation to format the exported file.
This can be caused by closing the instance of excel, then accidentally
opening it again by referencing something from the automation code.

If you post all your code that performs the export and automation, we can
probably spot where the problem is.


Jeanette Cunningham -- Melbourne Victoria Australia


<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Jeanette,
>
> FYI, your approach/logic is definitely leaves less "open instances" of
> Excel.
>
> That said, there is still one remaining "phantom" instance held open by
> Access.
>
> The reason this is important, is if I run my Format Excel worksheets macro
> via Access
> automation a 2nd time in the same instance of Access (where there is still
> one remaining "phantom"
> instance held open by Access) the formatting fails because Excel is still
> holding onto the objects
> or some other object-related issue.
>
> The reason I say this is I can "see" the instance of Excel in Task
> Manager. If I do not close the
> 1st Access instance (at which point the "phantom" instance of Excel
> disappears from Task Manager)
> and then re-open Access, the Excel formatting fails.
>
> I believe that the Access programmers need to figure out how to provide
> end users a VBA command to
> kill all instances of Excel opened via Access automation.
>
> There is no doubt in my mind that this Access automation bug is exactly
> why the Access Automation
> MVP's ignored this issue - direct or indirect pressure from MS to not give
> the bug legs.
>
> EagleOne
>
> "Jeanette Cunningham" <(E-Mail Removed)> wrote:
>
>>EagleOne,
>>here is part of the code I use successfully to format files in excel.
>>Below is the opening of excel and closing of excel part of the code.
>>
>>In your code, you first test to see if excel is already running.
>>In your closing part of the code you also can check to see if you opened
>>a
>>new instance of excel.
>>If you didn't need to open a new instance of excel, you don't need to
>>close
>>it.
>>
>>'opening excel here
>> If fIsAppRunning("excel", False) Then 'yes it is running
>> ' Get a reference to currently running Excel window
>> Set objXLApp = GetObject(, "Excel.Application")
>> blnExcelExists = True
>> Else
>> ' Excel is not currently running so create a new instance
>> Set objXLApp = CreateObject("Excel.Application")
>> End If
>>
>>
>>'closing excel here
>> 'close the instance of Excel created by code
>> If Not blnExcelExists Then
>> objXLApp.Quit
>> End If
>>
>> If Not objActiveWkb Is Nothing Then
>> Set objActiveWkb = Nothing
>> End If
>> If Not objXLApp Is Nothing Then
>> Set objXLApp = Nothing
>> End If
>> If Not db Is Nothing Then
>> Set db = Nothing
>> End If
>>
>>
>>
>>Jeanette Cunningham -- Melbourne Victoria Australia
>>
>>
>><(E-Mail Removed)> wrote in message
>>news:(E-Mail Removed)...
>>> 2003
>>>
>>> 2003 up to date
>>>
>>>
>>> If I use Access Automation to export Access data to Excel files, there
>>> is
>>> always one last instance of Excel in memory unti I close Access.
>>>
>>> Normally not a problem, unless I wish to do another export.
>>>
>>> What then occurs is that Access Automation cannot open any Excel files
>>> attached to that instance of Excel. In addition, Access VBA procedures
>>> which
>>> veryify Error status do not run.
>>>
>>> In fact, if I start the Access Automation macro without previously
>>> closing
>>> all instances of Excel, fail in the same manner as above.
>>>
>>> In short, to be successful with Access to Excel Automation, I must first
>>> close all Excel files and Excel itself. Also, if certain procedures in
>>> the
>>> Automation macros did not complete (do to error status check), I must
>>> close
>>> and restart Access.
>>>
>>> Thus, both Access must be "rebooted" and Excel must be created 1st time
>>> in
>>> that session.
>>>
>>> BTW I am using the Dev Ashish code somewhat tailored as below:
>>>
>>> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
>>> myPath As String, myDate As String, myTime As String)
>>> '
>>> 'Basic Code Courtesy of Dev Ashish
>>> '
>>>
>>> Dim objXL As Object
>>> Dim strWhat As String, boolXL As Boolean
>>> Dim MainSiteName As String
>>> Dim objActiveWkb As Object
>>> Dim Wks As Object
>>> Dim FullSheetRng As Object
>>> Dim OneColFilteredRng As Object
>>> Dim FullSheetFilteredRng As Object
>>> Dim ResizedRngToCopy As Object
>>> Dim myCell4OffHold As Object
>>> Dim myCell4Offset As Object
>>> Dim TempShtRng As Object
>>> Dim ResizedRngToDelete As Object
>>> Dim myCell As Object
>>> Dim ThisWorkbook As Object
>>> Dim myRowsToProcess As Long
>>> Dim myColumnsToProcess As Long
>>> Dim MaxRows As Long
>>> Dim MaxColumns As Long
>>> 'Following necessary for Sort sequences in Excel/Access 2003
>>> Dim xlSortOnValues As Long
>>> Dim xlAscending As Long
>>> Dim xlSortNormal As Long
>>> xlSortOnValues = 0
>>> xlAscending = 1
>>> xlSortNormal = 0
>>>
>>>
>>> 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
>>> .Visible = False
>>> 'Open the Workbook
>>> .Workbooks.Open myPathFile
>>> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
>>> End With
>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>>
>>> With objActiveWkb
>>> Set Wks = Nothing
>>> For Each Wks In .Worksheets
>>>
>>> Wks.Activate
>>>
>>> '
>>> '
>>> '
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> 'Many VBA lines of w/s formating
>>> '
>>> '
>>> '
>>>
>>> Sheets("Temp").Paste
>>> objXL.CutCopyMode = False
>>>
>>> objXL.DisplayAlerts = False
>>>
>>> objXL.DisplayAlerts = True
>>>
>>>
>>> Next wks
>>>
>>> objActiveWkb.Close savechanges:=True
>>>
>>> objXL.Application.Quit
>>> Set objActiveWkb = Nothing: Set objXL = Nothing
>>>
>>> 'Next section crude attempt to blow away extra Excel sessions
>>>
>>> If fIsAppRunning("Excel") Then
>>> Do While fIsAppRunning("Excel")
>>> On Error Resume Next
>>> Set objXL = GetObject(, "Excel.Application")
>>> On Error Resume Next
>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>> Err.Clear
>>> If Not objActiveWkb Is Nothing Then
>>> objXL.Application.Visible = True
>>> Do While Not objActiveWkb Is Nothing
>>> On Error Resume Next
>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>> On Error Resume Next
>>> objActiveWkb.Close savechanges:=True
>>> If objActiveWkb Is Nothing Then
>>> objXL.Application.Visible = False
>>> Exit Do
>>> End If
>>> Loop
>>> End If
>>> objXL.Application.Quit
>>> If objActiveWkb Is Nothing Then
>>> Set objActiveWkb = Nothing: Set objXL = Nothing
>>> Exit Do
>>> End If
>>> Loop
>>> End If
>>>
>>> End Sub
>>>
>>>
>>> Any suggestions as correct the challenge?
>>>
>>> TIA EagleOne
>>

Thanks for the offer.

Note that I have included the original code (before changes to your method)

Based upon your comments, I guess that my error is the sequence that I use in the 1st Sub next.


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 = Format(Time(), "hhmm")
myFileName = "ABC_AAASITE_TBL " & myDate & " " & myTime & ".xls"
myPathFile = myPath & myFileName
On Error Resume Next ' in case that the file does not exist
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"ABC_AAASITE_TBL", myPath & myFileName, True
If Not Err.Number > 0 Then
Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
End If
Err.Clear
myFileName = "XYZ_AAASITE_TBL " & myDate & " " & myTime & ".xls"
myPathFile = myPath & myFileName
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"XYZ_AAASITE_TBL", myPath & myFileName, True
If Not Err.Number > 0 Then
Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
End If
Err.Clear

End Sub

Sub FormatXLSheets(myPathFile As String, myFileName As String, _
myPath As String, myDate As String, myTime As String)
'
'Basic Code Courtesy of Dev Ashish
'

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim MainSiteName As String
Dim objActiveWkb As Object
Dim Wks As Object
Dim FullSheetRng As Object
Dim OneColFilteredRng As Object
Dim FullSheetFilteredRng As Object
Dim ResizedRngToCopy As Object
Dim myCell4OffHold As Object
Dim myCell4Offset As Object
Dim TempShtRng As Object
Dim ResizedRngToDelete As Object
Dim myCell As Object
Dim ThisWorkbook As Object
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long
Dim MaxRows As Long
Dim MaxColumns As Long
'Following necessary for Sort sequences in Excel/Access 2003
Dim xlSortOnValues As Long
Dim xlAscending As Long
Dim xlSortNormal As Long
xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0


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
.Visible = False
'Open the Workbook
.Workbooks.Open myPathFile
'.ActiveWorkBook.RunAutoMacros xlAutoOpen
End With
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb
Set Wks = Nothing
For Each Wks In .Worksheets

Wks.Activate

'
'
'
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'
'
'

Sheets("Temp").Paste
objXL.CutCopyMode = False

objXL.DisplayAlerts = False

objXL.DisplayAlerts = True


Next wks

objActiveWkb.Close savechanges:=True

objXL.Application.Quit
Set objActiveWkb = Nothing: Set objXL = Nothing

'Next section crude attempt to blow away extra Excel sessions

If fIsAppRunning("Excel") Then
Do While fIsAppRunning("Excel")
On Error Resume Next
Set objXL = GetObject(, "Excel.Application")
On Error Resume Next
Set objActiveWkb = objXL.Application.ActiveWorkbook
Err.Clear
If Not objActiveWkb Is Nothing Then
objXL.Application.Visible = True
Do While Not objActiveWkb Is Nothing
On Error Resume Next
Set objActiveWkb = objXL.Application.ActiveWorkbook
On Error Resume Next
objActiveWkb.Close savechanges:=True
If objActiveWkb Is Nothing Then
objXL.Application.Visible = False
Exit Do
End If
Loop
End If
objXL.Application.Quit
If objActiveWkb Is Nothing Then
Set objActiveWkb = Nothing: Set objXL = Nothing
Exit Do
End If
Loop
End If

End Sub


"Jeanette Cunningham" <(E-Mail Removed)> wrote:

>EagleOne,
>I have seen this problem when using either OutputTo or TransferSpreadsheet
>to export to excel then using automation to format the excel file.
>If you are using the above process, finish the export completely before
>starting any code that uses automation to format the exported file.
>
>The other usual reason for this problem, is related to how you structure the
>code that uses automation to format the exported file.
>This can be caused by closing the instance of excel, then accidentally
>opening it again by referencing something from the automation code.
>
>If you post all your code that performs the export and automation, we can
>probably spot where the problem is.
>
>
>Jeanette Cunningham -- Melbourne Victoria Australia
>
>
><(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> Jeanette,
>>
>> FYI, your approach/logic is definitely leaves less "open instances" of
>> Excel.
>>
>> That said, there is still one remaining "phantom" instance held open by
>> Access.
>>
>> The reason this is important, is if I run my Format Excel worksheets macro
>> via Access
>> automation a 2nd time in the same instance of Access (where there is still
>> one remaining "phantom"
>> instance held open by Access) the formatting fails because Excel is still
>> holding onto the objects
>> or some other object-related issue.
>>
>> The reason I say this is I can "see" the instance of Excel in Task
>> Manager. If I do not close the
>> 1st Access instance (at which point the "phantom" instance of Excel
>> disappears from Task Manager)
>> and then re-open Access, the Excel formatting fails.
>>
>> I believe that the Access programmers need to figure out how to provide
>> end users a VBA command to
>> kill all instances of Excel opened via Access automation.
>>
>> There is no doubt in my mind that this Access automation bug is exactly
>> why the Access Automation
>> MVP's ignored this issue - direct or indirect pressure from MS to not give
>> the bug legs.
>>
>> EagleOne
>>
>> "Jeanette Cunningham" <(E-Mail Removed)> wrote:
>>
>>>EagleOne,
>>>here is part of the code I use successfully to format files in excel.
>>>Below is the opening of excel and closing of excel part of the code.
>>>
>>>In your code, you first test to see if excel is already running.
>>>In your closing part of the code you also can check to see if you opened
>>>a
>>>new instance of excel.
>>>If you didn't need to open a new instance of excel, you don't need to
>>>close
>>>it.
>>>
>>>'opening excel here
>>> If fIsAppRunning("excel", False) Then 'yes it is running
>>> ' Get a reference to currently running Excel window
>>> Set objXLApp = GetObject(, "Excel.Application")
>>> blnExcelExists = True
>>> Else
>>> ' Excel is not currently running so create a new instance
>>> Set objXLApp = CreateObject("Excel.Application")
>>> End If
>>>
>>>
>>>'closing excel here
>>> 'close the instance of Excel created by code
>>> If Not blnExcelExists Then
>>> objXLApp.Quit
>>> End If
>>>
>>> If Not objActiveWkb Is Nothing Then
>>> Set objActiveWkb = Nothing
>>> End If
>>> If Not objXLApp Is Nothing Then
>>> Set objXLApp = Nothing
>>> End If
>>> If Not db Is Nothing Then
>>> Set db = Nothing
>>> End If
>>>
>>>
>>>
>>>Jeanette Cunningham -- Melbourne Victoria Australia
>>>
>>>
>>><(E-Mail Removed)> wrote in message
>>>news:(E-Mail Removed)...
>>>> 2003
>>>>
>>>> 2003 up to date
>>>>
>>>>
>>>> If I use Access Automation to export Access data to Excel files, there
>>>> is
>>>> always one last instance of Excel in memory unti I close Access.
>>>>
>>>> Normally not a problem, unless I wish to do another export.
>>>>
>>>> What then occurs is that Access Automation cannot open any Excel files
>>>> attached to that instance of Excel. In addition, Access VBA procedures
>>>> which
>>>> veryify Error status do not run.
>>>>
>>>> In fact, if I start the Access Automation macro without previously
>>>> closing
>>>> all instances of Excel, fail in the same manner as above.
>>>>
>>>> In short, to be successful with Access to Excel Automation, I must first
>>>> close all Excel files and Excel itself. Also, if certain procedures in
>>>> the
>>>> Automation macros did not complete (do to error status check), I must
>>>> close
>>>> and restart Access.
>>>>
>>>> Thus, both Access must be "rebooted" and Excel must be created 1st time
>>>> in
>>>> that session.
>>>>
>>>> BTW I am using the Dev Ashish code somewhat tailored as below:
>>>>
>>>> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
>>>> myPath As String, myDate As String, myTime As String)
>>>> '
>>>> 'Basic Code Courtesy of Dev Ashish
>>>> '
>>>>
>>>> Dim objXL As Object
>>>> Dim strWhat As String, boolXL As Boolean
>>>> Dim MainSiteName As String
>>>> Dim objActiveWkb As Object
>>>> Dim Wks As Object
>>>> Dim FullSheetRng As Object
>>>> Dim OneColFilteredRng As Object
>>>> Dim FullSheetFilteredRng As Object
>>>> Dim ResizedRngToCopy As Object
>>>> Dim myCell4OffHold As Object
>>>> Dim myCell4Offset As Object
>>>> Dim TempShtRng As Object
>>>> Dim ResizedRngToDelete As Object
>>>> Dim myCell As Object
>>>> Dim ThisWorkbook As Object
>>>> Dim myRowsToProcess As Long
>>>> Dim myColumnsToProcess As Long
>>>> Dim MaxRows As Long
>>>> Dim MaxColumns As Long
>>>> 'Following necessary for Sort sequences in Excel/Access 2003
>>>> Dim xlSortOnValues As Long
>>>> Dim xlAscending As Long
>>>> Dim xlSortNormal As Long
>>>> xlSortOnValues = 0
>>>> xlAscending = 1
>>>> xlSortNormal = 0
>>>>
>>>>
>>>> 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
>>>> .Visible = False
>>>> 'Open the Workbook
>>>> .Workbooks.Open myPathFile
>>>> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
>>>> End With
>>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>>>
>>>> With objActiveWkb
>>>> Set Wks = Nothing
>>>> For Each Wks In .Worksheets
>>>>
>>>> Wks.Activate
>>>>
>>>> '
>>>> '
>>>> '
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> 'Many VBA lines of w/s formating
>>>> '
>>>> '
>>>> '
>>>>
>>>> Sheets("Temp").Paste
>>>> objXL.CutCopyMode = False
>>>>
>>>> objXL.DisplayAlerts = False
>>>>
>>>> objXL.DisplayAlerts = True
>>>>
>>>>
>>>> Next wks
>>>>
>>>> objActiveWkb.Close savechanges:=True
>>>>
>>>> objXL.Application.Quit
>>>> Set objActiveWkb = Nothing: Set objXL = Nothing
>>>>
>>>> 'Next section crude attempt to blow away extra Excel sessions
>>>>
>>>> If fIsAppRunning("Excel") Then
>>>> Do While fIsAppRunning("Excel")
>>>> On Error Resume Next
>>>> Set objXL = GetObject(, "Excel.Application")
>>>> On Error Resume Next
>>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>>> Err.Clear
>>>> If Not objActiveWkb Is Nothing Then
>>>> objXL.Application.Visible = True
>>>> Do While Not objActiveWkb Is Nothing
>>>> On Error Resume Next
>>>> Set objActiveWkb = objXL.Application.ActiveWorkbook
>>>> On Error Resume Next
>>>> objActiveWkb.Close savechanges:=True
>>>> If objActiveWkb Is Nothing Then
>>>> objXL.Application.Visible = False
>>>> Exit Do
>>>> End If
>>>> Loop
>>>> End If
>>>> objXL.Application.Quit
>>>> If objActiveWkb Is Nothing Then
>>>> Set objActiveWkb = Nothing: Set objXL = Nothing
>>>> Exit Do
>>>> End If
>>>> Loop
>>>> End If
>>>>
>>>> End Sub
>>>>
>>>>
>>>> Any suggestions as correct the challenge?
>>>>
>>>> TIA EagleOne
>>>
>

Hi,
I tested your code in a test database.
Here is the code that worked well.
It worked if Excel was closed before the export and also if excel was in use
before the export.
I suggest that you create a new test form in your database, copy and paste
this code and try it just as it is with all the formatting code commented
out.
See if it runs OK and doesn't leave an instance of excel hanging.

Now try it with all the formatting code in action and see if the formatting
code is causing the problem of leaving an instance of excel hanging.




Here is the code.
-------------------------Option Compare Database
Option Explicit

Private Sub cmdExport_Click()
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 = Format(Time(), "hhmm")
myFileName = "EagleOne " & myDate & " " & myTime & ".xls"
myPathFile = myPath & myFileName
On Error Resume Next ' in case that the file does not exist
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryEmpTYDH", myPath & myFileName, True
If Not Err.Number > 0 Then
Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
End If


Err.Clear
myFileName = "EagleTwo " & myDate & " " & myTime & ".xls"
myPathFile = myPath & myFileName
On Error Resume Next
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
"qryEmpTYDH", myPath & myFileName, True
If Not Err.Number > 0 Then
Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
End If
Err.Clear

End Sub

Sub FormatXLSheets(myPathFile As String, myFileName As String, _
myPath As String, myDate As String, myTime As String)
'
'Basic Code Courtesy of Dev Ashish
'

Dim objXL As Object
Dim strWhat As String, boolXL As Boolean
Dim MainSiteName As String
Dim objActiveWkb As Object
Dim Wks As Object
Dim FullSheetRng As Object
Dim OneColFilteredRng As Object
Dim FullSheetFilteredRng As Object
Dim ResizedRngToCopy As Object
Dim myCell4OffHold As Object
Dim myCell4Offset As Object
Dim TempShtRng As Object
Dim ResizedRngToDelete As Object
Dim myCell As Object
Dim ThisWorkbook As Object
Dim myRowsToProcess As Long
Dim myColumnsToProcess As Long
Dim MaxRows As Long
Dim MaxColumns As Long
'Following necessary for Sort sequences in Excel/Access 2003
Dim xlSortOnValues As Long
Dim xlAscending As Long
Dim xlSortNormal As Long
xlSortOnValues = 0
xlAscending = 1
xlSortNormal = 0


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
.Visible = False
'Open the Workbook
.Workbooks.Open myPathFile
'.ActiveWorkBook.RunAutoMacros xlAutoOpen
End With
Set objActiveWkb = objXL.Application.ActiveWorkbook

With objActiveWkb
Set Wks = Nothing
For Each Wks In .Worksheets

Wks.Activate

'
'
'
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'Many VBA lines of w/s formating
'
'
'

' Sheets("Temp").Paste
objXL.CutCopyMode = False

objXL.DisplayAlerts = False

objXL.DisplayAlerts = True


Next Wks
End With

objActiveWkb.Close savechanges:=True

'close the instance of Excel created by code
If boolXL = True Then
objXL.Quit
End If
Set objActiveWkb = Nothing: Set objXL = Nothing

End Sub
-------------------------


Jeanette Cunningham -- Melbourne Victoria Australia



<(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> Thanks for the offer.
>
> Note that I have included the original code (before changes to your
> method)
>
> Based upon your comments, I guess that my error is the sequence that I use
> in the 1st Sub next.
>

Thank you so much for your time and knowledge!

I'll attempt the changes 1st thing in my morning. If available, check back in about 12+ hours.

The reason I left all of the Dim statements applicable to the formatting procedures was if you saw
any clue that my Dim statements did not inappropriately declare objects.

Your idea is best, ascertain if the sequencing did not already solve the challenge.

EagleOne

"Jeanette Cunningham" <(E-Mail Removed)> wrote:

>Hi,
>I tested your code in a test database.
>Here is the code that worked well.
>It worked if Excel was closed before the export and also if excel was in use
>before the export.
>I suggest that you create a new test form in your database, copy and paste
>this code and try it just as it is with all the formatting code commented
>out.
>See if it runs OK and doesn't leave an instance of excel hanging.
>
>Now try it with all the formatting code in action and see if the formatting
>code is causing the problem of leaving an instance of excel hanging.
>
>
>
>
>Here is the code.
>-------------------------Option Compare Database
>Option Explicit
>
>Private Sub cmdExport_Click()
> 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 = Format(Time(), "hhmm")
> myFileName = "EagleOne " & myDate & " " & myTime & ".xls"
> myPathFile = myPath & myFileName
> On Error Resume Next ' in case that the file does not exist
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
> "qryEmpTYDH", myPath & myFileName, True
> If Not Err.Number > 0 Then
> Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
> End If
>
>
> Err.Clear
> myFileName = "EagleTwo " & myDate & " " & myTime & ".xls"
> myPathFile = myPath & myFileName
> On Error Resume Next
> DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel8, _
> "qryEmpTYDH", myPath & myFileName, True
> If Not Err.Number > 0 Then
> Call FormatXLSheets(myPathFile, myFileName, myPath, myDate, myTime)
> End If
> Err.Clear
>
>End Sub
>
> Sub FormatXLSheets(myPathFile As String, myFileName As String, _
> myPath As String, myDate As String, myTime As String)
> '
> 'Basic Code Courtesy of Dev Ashish
> '
>
> Dim objXL As Object
> Dim strWhat As String, boolXL As Boolean
> Dim MainSiteName As String
> Dim objActiveWkb As Object
> Dim Wks As Object
> Dim FullSheetRng As Object
> Dim OneColFilteredRng As Object
> Dim FullSheetFilteredRng As Object
> Dim ResizedRngToCopy As Object
> Dim myCell4OffHold As Object
> Dim myCell4Offset As Object
> Dim TempShtRng As Object
> Dim ResizedRngToDelete As Object
> Dim myCell As Object
> Dim ThisWorkbook As Object
> Dim myRowsToProcess As Long
> Dim myColumnsToProcess As Long
> Dim MaxRows As Long
> Dim MaxColumns As Long
> 'Following necessary for Sort sequences in Excel/Access 2003
> Dim xlSortOnValues As Long
> Dim xlAscending As Long
> Dim xlSortNormal As Long
> xlSortOnValues = 0
> xlAscending = 1
> xlSortNormal = 0
>
>
> 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
> .Visible = False
> 'Open the Workbook
> .Workbooks.Open myPathFile
> '.ActiveWorkBook.RunAutoMacros xlAutoOpen
> End With
> Set objActiveWkb = objXL.Application.ActiveWorkbook
>
> With objActiveWkb
> Set Wks = Nothing
> For Each Wks In .Worksheets
>
> Wks.Activate
>
> '
> '
> '
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> 'Many VBA lines of w/s formating
> '
> '
> '
>
>' Sheets("Temp").Paste
> objXL.CutCopyMode = False
>
> objXL.DisplayAlerts = False
>
> objXL.DisplayAlerts = True
>
>
> Next Wks
> End With
>
> objActiveWkb.Close savechanges:=True
>
> 'close the instance of Excel created by code
> If boolXL = True Then
> objXL.Quit
> End If
> Set objActiveWkb = Nothing: Set objXL = Nothing
>
> End Sub
>-------------------------
>
>
>Jeanette Cunningham -- Melbourne Victoria Australia
>
>
>
><(E-Mail Removed)> wrote in message
>news:(E-Mail Removed)...
>> Thanks for the offer.
>>
>> Note that I have included the original code (before changes to your
>> method)
>>
>> Based upon your comments, I guess that my error is the sequence that I use
>> in the 1st Sub next.
>>
>