Problems exporting Data to Excel

[ohioboy]

I've worked 3 hours to solve this problem and I can't.

I'm trying to write code to move data into an excel spreadsheet. When I first open Access and run the code everything works fine. As you can see from my code, data is placed in excel and the worksheet is left on the screen for me to view. After I close excel and immediately run the same code excel will open but it does not appear to open my worksheet. As a matter of fact it kind of freezes as it only captures a picture of the screen. If I then close excel and open it from the desktop and then open the worksheet everything's fine. I can close and reopen Access and it would again work 1 time before this problem.

Here's my code:
Dim app As Excel.Application
Dim wb As Excel.Workbook
Dim sht As Excel.Worksheet
Dim rng As Excel.Range
Dim cht As Excel.Chart

Set app = Excel.Application
app.Visible = True
Set wb = Workbooks.Open("TestTemplate.xls")
Set sht = wb.ActiveSheet

sht.Cells(4, 4) = "Test"

Any thoughts on why this is happening?

[nico5038]

Did you try closing the objects after filling the cells like:

Set sht = Nothing
Set wb = Nothing
app.quit
 

Nic;o)

[ohioboy]

I tried that and it didn't work. This must be something simple that I'm missing but I can't figure it out. Any other ides?

[nico5038]

Hmm, I used the following code myself in the past without this trouble:

Create a new module "modAPI" with:

Option Compare Database
Option Explicit
 
Declare Function acb_apiFindWindow Lib "user32" Alias "FindWindowA" (ByVal strClassName As String, ByVal lpWindowName As Any) As Long
 
Function acbIsWordLoaded() As Long
    ' Report if a Word application is currently loaded
    ' In:
    '     strClassName: the Windows class name for the main window
    '                   of the app to check for.
    ' Out:
    '     Return Value: 0 if App isn't loaded, non-zero (the
    '         app's hWnd) otherwise.
 
   acbIsWordLoaded = acb_apiFindWindow("OpusApp", 0&)
 
End Function
Function acbIsExcelLoaded() As Long
    ' Report if a Word application is currently loaded
    ' In:
    '     strClassName: the Windows class name for the main window
    '                   of the app to check for.
    ' Out:
    '     Return Value: 0 if App isn't loaded, non-zero (the
    '         app's hWnd) otherwise.
 
   acbIsExcelLoaded = acb_apiFindWindow("XLmain", 0&)
 
End Function
 

Now we can test or there's already an excel app running before opening the file and the closure should now work OK.

Function fncExportExcel()
 
Dim appExcel As Excel.Application
Dim wkbExcel As Excel.Workbook
Dim chtExcel As Excel.Chart
Dim strFile As String
 
If acbIsExcelLoaded Then
   Set appExcel = Excel.Application
Else
   Set appExcel = New Excel.Application
End If
 
strFile = "c:\xxx\xls"
Set wkbExcel = appExcel.Workbooks.Open(strFile)
 
wkbExcel.Sheets("Sheet1").Cells(4, 4) = "Test"
 
wkbExcel.Save
appExcel.Application.Workbooks.Close
 
End Function
 

Nic;o)