Could not find installable ISAM error when creating Excel file from Access macro

[Margarita]

Hello, I am working in Access and Excel 2003. Here is my goal: to create a copy of an existing Excel template in vba from access, paste results of a vba-defined query recordset into the Excel workbook and open the workbook. My entire code is below. To give a little detail: the macro is supposed to create a query that joins two tables (Task and Pay)- giving details on how much employees made per task. The Excel template has several sheets, where A1 contains the name of the respective tasks. I am getting a 'could not find an installable ISAM' error where I try to open a connection between access and excel.

What I have done so far is verify that the file Msexcl40.dll is registered under the correct path in the Registry Editor, as outlined in the Microsoft help article. I also ran an Office installation repair from Control Panel. That didn't help. I have absolutely no knowledge about setting up connections between Excel and Access, so please let me know if I am missing something very basic.

Sub squery()
Dim dbs As Database
Dim cnn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim TaskString As String
Dim strSQL As String, contract As String
Dim intRow As Integer
Dim i As Integer
Set dbs = CurrentDb
Dim countrecords As Long
 
 
 
 
strSQL = "Select * from Pay inner join Task on Pay.EMPLID= Task.EMPLID "
 
'Dim xlApplication As Excel.Application
'Dim xlWorkbook As Excel.Workbook
Set xlApplication = CreateObject("Excel.Application")
xlApplication.Visible = False
 
FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\ Test Report output"
 
'this is where I get the error:
cnn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"H:\WTC BUDGET Reporting\WTC TEST\ Test Report output;" & _
"Extended Properties=""Excel 8.0;HDR=Yes"";"
 
Set rs = New ADODB.Recordset
rs.ActiveConnection = cn
 
For Each ws In Activeworkbook.worksheets
 
TaskString = ws.range("A1").Value
strSQL2 = strSQL & "where FTE.Task like '" & TaskString & "';"
Set qrydef = dbs.CreateQueryDef(testquery, strSQL2)
Set rs = qrydef.OpenRecordset
rs.MoveLast
countrecords = rs.RecordCount
rs.MoveFirst
Activesheet.range("A" & countrecords + 2 & ":A2").entirerow.insert
Actoveworkbook.Activesheet.Cells.CopyFromRecordset rs
rs.Close
Next
 
xlApplication.Visible = True
Activeworkbook.Save
Activeworkbook.Open
 
cn.Close
End Sub

 

[boblarson]

What I have done so far is verify that the file Msexcl40.dll is registered under the correct path in the Registry Editor, as outlined in the Microsoft help article.

I don't understand exactly why you are trying to use ADO on the Excel file. Aren't you trying to put the results of an Access query into Excel?

If so, you don't need the ADO at all.

Also, your FileCopy is wrong as it doesn't have the file name and extension in the destination. It needs the whole thing, not just the folder, or not just the name, but the extension too.


So, here's my take on it:

   Dim dbs    As DAO.Database
    Dim rs     As DAO.Recordset
    Dim TaskString As String
    Dim xlApp  As Object
    Dim ws     As Object
    Dim strSQL As String, contract As String
    Dim intRow As Integer
    Dim i      As Integer
    Dim countrecords As Long
    Set dbs = CurrentDb
 

    strSQL = "Select * from Pay inner join Task on Pay.EMPLID= Task.EMPLID "
    Set xlApp = CreateObject("Excel.Application")
    '    xlApp.Visible = False (it will already be false so no need to set it false)
    FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\ Test Report output.xls"
 
 
    For Each ws In xlApp.ActiveWorkbook.Worksheets
        TaskString = ws.Range("A1").value
        strSQL2 = strSQL & "where FTE.Task = " & Chr(34) & TaskString & Chr(34) & ";"
        Set rs = dbs.OpenRecordset(strSQL2)
        rs.MoveLast
        countrecords = rs.RecordCount
        rs.MoveFirst
        ws.Cells.CopyFromRecordset rs
        rs.Close
    Next
    xlApp.Visible = True
    xlApp.ActiveWorkbook.Save
    
    Set rs = Nothing
End Sub

Now I might have missed why you had this (I removed it):

[FONT=Courier New][COLOR=#dd0000]Activesheet.range("[/COLOR][COLOR=#0000bb]A[/COLOR][COLOR=#dd0000]" & countrecords + 2 & "[/COLOR][COLOR=#007700]:[/COLOR][COLOR=#0000bb]A2[/COLOR][/FONT][COLOR=#dd0000][FONT=Courier New]").entirerow.insert[/FONT]

[/COLOR]

so if you could explain that one more, we can make a suggestion on how to better deal with it.

 

[Margarita]

Hi Bob, thank you so much for your reply! To explain my resoning behind inserting a range of rows before pasting: the reason I wanted to grab and insert the entire strSQL2 recordset into inserted lines is that the way I have the template set up, the second section of the report starts right under the header row of the first section, so I can't actually paste over the second section- I need to insert the desired recordset between Section 1 and Section 2 headers.

To follow up on the code- thank you again for taking the time to give this a shot!- when I run it, it does successfully create the test report output workbook, but spits out a 'Run time error 91: object variable or With block not set' at the first line of the loop:
'For Each ws In xlApp.ActiveWorkbook.Worksheets'

I am currently trying to figure out what is causing it.

Thank you!

 

[boblarson]

To answer your question about the insert of rows, you can do a quick search to find the end of Section 1 and, if I knew what we were looking at I can give you more of a suggestion on how to do it without having to connect to Excel using a recordset for it. You still use a recordset for the data to send to Excel FROM Access.

It may not like that WS is declared as object in that instance. You can try changing it to VARIANT and see if that helps.

 

[Margarita]

Hello,
I wasn't aware of a way to get the results of these queries into an excel template that is already created other than by creating these successive recordsets in a loop that reads of the task string and passes it to the sql string. How could I paste the results without resorting to recordsets? Any suggestions would be much appreciated.

Back to my issue with having to insert lines before pasting- maybe I am being dense, but I don't see any other way to do the pasting without touching the sections that are below section 1. Basically, the way I have it set up is: in row 1 there are the section 1 column headings. Row 2 is Totals for Section 1 which contains formulas that will sum everything above it once the recordset rows are inserted. In row 4 is the beginning of section 2, with the totals line in Line 5, and so on. That's why I rely on inserting lines. Please let me know if there is a more efficient way to do this.

I did try dim ws as variant, but still getting the same error. I will crack this one though!
Thank you!

 

[boblarson]

It isn't a problem to insert the number of rows that you need between row 1 and row 2 (and hence the others too for that matter).

If possible I would start with the bottom sections first and work my way up instead of the other way around. That way you know where your template starts for each section and you can insert the number of rows for each section by the count of the recordset.

What formula are you using in the template so that when you insert rows, it still totals the rows above? I haven't been able to get one like that to work. I set the formulas after inserting myself.

 

[Margarita]

To get the formulas in the totals rows without doing it manually (though this is only semi-automated) I wrote a short macro in excel that would call the address of the cells where I want the totals as PayTotal1, FringeTotal1, etc for example. Then the formula would sum everything in that column up to the previous total. So for section 1 the sum in cell named PayTotal1 would be:
sum of range("B1:B" PayTotal1.row-1)
for section 2:
sum of range("B" & PayTotal1.row+1 & ":B" & PayTotals2.row-1)
etc

This is probably no more efficient than doing the formulas manually after everything is pasted, but having to hit the run macro button once appeals more to me than putting in the formulas every time. Let's see if I can actually pull off having the whole report come together with these macros...

 

[Margarita]

Hello, for anyone looking to this string for guidance, I have finally gotten the code to work the way I want it with the help of the fantastic folks here and at MrExcel forums. I am posting it here if anyone else has similar issues:

Sub boblarsontest()
Dim dbs As DAO.Database
Dim rs As DAO.Recordset
Dim TaskString As String
Dim xlApp As Object
Dim ws As Variant
Dim strSQL As String, contract As String
Dim intRow As Integer
Dim i As Integer
Dim countrecords As Long
Set dbs = CurrentDb
Dim x As Integer
Set xlApp = CreateObject("Excel.Application")

Dim copypath As String
copypath = "H:\BUDGET Reporting\TEST\Test Report output.xls"

If FileOrDirExists(copypath) Then
Debug.Print "wb exists"
Else
On Error Resume Next
FileCopy Source:="H:\BUDGET Reporting\TEST\test report template.xls", Destination:="H:\BUDGET Reporting\TEST\Test Report output.xls"

End If
Dim wb As Object
Set wb = xlApp.Workbooks.Open("H:\BUDGET Reporting\TEST\Test Report output.xls")
x = wb.worksheets.Count
strSQL = "Select * from Pay inner join FTE on Pay.EMPLID= FTE.EMPLID "

For i = 1 To x
Debug.Print "loop started"
Set ws = wb.worksheets(i)
Debug.Print "worksheet set"
TaskString = ws.range("A1").Value
Debug.Print TaskString
Debug.Print "taskstring done"
strSQL2 = strSQL & "where FTE.Task like " & Chr(34) & TaskString & Chr(34) & ";"
Debug.Print "sql string set"
Set rs = dbs.OpenRecordset(strSQL2)
rs.MoveLast
Debug.Print "move last"
countrecords = rs.RecordCount
Debug.Print "recordcount done"
rs.MoveFirst
ws.range("A" & countrecords + 2 & ":A2").entirerow.insert
Debug.Print "rows inserted"
ws.range("A3").CopyFromRecordset rs
Debug.Print "pasted"
rs.Close
Next