Updating Excel Worksheet from Access

[jimmyjot]

Hi,

I am using VBA code in an Access 2007 database to update an Excel 2007 spreadsheet. If I have the spreadsheet and the module open, and execute the code with the Run command from the module, it works as expected. However, if I run the code with Excel closed, or if I try to execute the code through a macro to run the module, nothing happens.

Code is included here:

Option Compare Database
Option Explicit
Public myExcel As Excel.Application
Public Function CopyToExcel()
Dim conn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim wbk As Excel.Workbook
Dim wks As Excel.Worksheet
Dim StartRange As Excel.Range
Dim strConn As String
Dim i As Integer
Dim f As Variant

On Error GoTo ErrorHandler

strConn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source = " & CurrentDb.Name

Set conn = New ADODB.Connection

Set rst = New ADODB.Recordset
With rst
.Open "GamesNamesRatings_Crosstab", strConn, _
adOpenKeyset, adLockOptimistic
End With

Set myExcel = New Excel.Application
myExcel.Visible = True
Set wbk = myExcel.Workbooks.Open(CurrentProject.Path & "\GamesNamesRatings.xlsx")
Set wks = wbk.Worksheets(1)
' wks.Visible = True


i = 1
With rst
For Each f In .Fields
With wks
.Cells(1, i).Value = f.Name
i = i + 1
End With
Next
End With

Set StartRange = wks.Cells(2, 1)
StartRange.CopyFromRecordset rst

rst.Close
Set rst = Nothing

wks.Columns.AutoFit

wbk.Close SaveChanges:=True

myExcel.Quit
Set conn = Nothing
Exit Function

ErrorHandler:
MsgBox Err.Description, vbCritical, "Automation Error"
Set myExcel = Nothing
End Function

Is there some security or permission settings I am omitting, or is there something else I need to do?

Thanks,

Jim

 

[SpentGeezer]

Try replacing this:

Set myExcel = New Excel.Application
myExcel.Visible = True
Set wbk = myExcel.Workbooks.Open(CurrentProject.Path & "\GamesNamesRatings.xlsx")
Set wks = wbk.Worksheets(1)
' wks.Visible = True

with something like this:

Set myExcel = CreateObject("Excel.Application")
myExcel.Visible = true
myExcel.Workbooks.Open CurrentProject.Path & "\GamesNamesRatings.xlsx"
Set wks = objExcel.ActiveWorkbook.Worksheets(1)