Access to Excel errors

[Guest]

Hello all,

I'm a novice in using VBA in Access. Now I'm facing the problem that I want
Access to update/create data in an Excel-file, but in my code I encounter
error messages.
You find hereafter the code and also the error messages I'm encounter.
Thanks for looking at it and hping that some experts can help me out

Public Sub GenCount()


'Count on "Abecnr" and on "CalPPP" per datum for the actual ReportType
rs_Graph.Close
rs_Graph.Open "SELECT Tbl_Graphics.CALPPP, Tbl_Graphics.Datum,
Count(Tbl_Graphics.Abecnr) AS CountOfAbecnr, " & _
"Tbl_Graphics.RepType FROM Tbl_Graphics " & _
"GROUP BY Tbl_Graphics.CALPPP, Tbl_Graphics.Datum,
Tbl_Graphics.RepType;"

Dim intMaxCol As Integer
Dim intMaxRow As Integer
Dim oSource As Object 'Excel.Application 'Speed up performance.
Dim StrSource As String
Dim DestSource As String
Dim objWbk As Workbook
Dim objSht As Worksheet
Set oSource = excel.Application 'Run-time Error 424 Object required
StrSource = "I:\be\_agb\agfaproj\logistiek\WOS\BackOrdersGraph.xls"
'Place workbook in an object to call the sheet(s) later
Set objWbk = oSource.Workbooks.Open(StrSource) 'Run-time Error 13 Type
mismatch
oSource.Visible = True 'Visible during
intMaxCol = rs_Graph.Fields.Count
If rs_Graph.RecordCount > 0 Then
rs_Graph.MoveLast: rs_Graph.MoveFirst
intMaxRow = rs_Graph.RecordCount
ShtNme = m_RepType
On Error GoTo addsheet:
Set objSht = objWbk.Worksheets(ShtNme) 'sheet is a part of the
workbook , not from the excel application itself
On Error GoTo 0
With objSht
.Range(.Cells(1, 1), .Cells(intMaxRow, _
intMaxCol)).CopyFromRecordset rs_Graph
End With
objWbk.Close , True 'Save and close workbook 'Compile error: Method
or data member not found
Else
objWbk.Close , True 'Save and close workbook 'Compile error:
Method or data member not found
End If

If oSource.Workbooks.Count = 0 Then oSource.Quit 'Close Excel

Exit Sub 'leave the routine, hereafter follows the error-trapping

addsheet:
Set objSht = objWbk.Worksheets.Add ' add sheet to the workbook
'Run-time Error 91 Object variable with Block variable not set
objSht.Name = m_RepType
Resume 'back to where the fault began

 

[Guest]

Hi Amduke,

Just had a quick look, and it appears that you haven't created a new
instance of MS Excel. Because you're using late binding, you'll need to
replace the line "Set oSource = excel.Application" with:

Set oSource = CreateObject("Excel.Application")

If you had declared the variable oSource as Excel.Application you would have
replaced it with the following line instead.

Set oSource = New Excel.Application

Haven't tested it, and it is just based on first appearances, but I hope it
helps!

Stuart

 

[Guest]

Hello Stuart,

thanks for the advise.
Thoug another issue caused the problem. In my references there was no
"Microsoft Excel object library" selected. This was the major reason for the
error codes generated during the process.