Solved Creating Excel Files from Access (1 Viewer)

[Drand]

Hi

I am trying to create 134 Excel Macro Enable workbooks from my Access application.

My code is

Public Function CreateDataFiles()

Dim xlApp As Object
Dim xlWB As Object
Dim strFileName As String
Set xlWB = Workbooks.Add("C:\KPMG\Excel Templates\Member Firm DataFile.xlsm")

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT * FROM qryDataFileDetails")


If Not (rs.EOF And rs.BOF) Then
    rs.MoveFirst
    Do Until rs.EOF = True
    
    strFileName = "C:\KPMG\TestingDataFiles\" & rs!FileName
     
Set xlApp = CreateObject("Excel.Application")


xlApp.ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=52
ActiveWorkbook.Close


        'Move to the next record. Don't ever forget to do this.
        rs.MoveNext

Loop

End If

rs.Close
Set rs = Nothing




xlApp.Quit
Set xlApp = Nothing
xlWB.Close True
End Function

The code creates the first file but fails at line "xlApp.ActiveWorkbook.SaveAs FileName:=strFileName, FileFormat:=52" when it loops to the second record.

The error message is "Run time Error 91, Object Variable or With block variable not set"

Could someone please tell me where I am going wrong with this?

Many thanks

 

[arnelgp]

you can use Another alternative of doing it by Copying and the Template Excel file:

Public Function CreateDataFiles()

    Const TemplateFile As String = "C:\KPMG\Excel Templates\Member Firm DataFile.xlsm"
    Const TargetFolder As String = "C:\KPMG\TestingDataFiles\"
   
   
    With CurrentDb.OpenRecordset("SELECT * FROM qryDataFileDetails", dbOpenSnapshot, dbReadOnly)
       
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End
   
   
        Do Until .EOF
           
            If Len(!FileName & "") <> 0 Then
                ' if the file already exists, what do you want to do
                ' delete it?
                'If Len(Dir$(TargetFolder & !FileName)) <> 0 Then
                '    Kill TargetFolder & !FileName
                'End If
                VBA.FileCopy TemplateFile, TargetFolder & !FileName
            End If
            .MoveNext
        Loop
       
        .Close
    End With

End Function

also, the code did not check if TargetFolder already exists.

 

[Drand]

Thanks for the resposne

I copied your code and am getting End With without With when I compile it

 

[Gasman]

Try using End If and not End to terminate an If statement?
I would ask why you are creating an excel object each time within the loop, and not just once outside the loop?

 

[arnelgp]

change this:

If Not (.BOF And .EOF) Then
.MoveFirst
End

To:

If Not (.BOF And .EOF) Then
.MoveFirst
End If

 

[Drand]

Absolutely brilliant - thank you.

What you gave me is so much simpler than what I was attempting to do.

 

[ebs17]

'       If Not (.BOF And .EOF) Then
'            .MoveFirst
'       End

       Do Until .EOF
       ...

The check used here is superfluous, since the record pointer is automatically set to the first record in a newly opened recordset, and then to EOF in the case of an empty recordset. The termination condition in the loop is therefore sufficient.

It would be a bit different if the recordset takes over a form recordset directly, since the record pointer could have been moved somewhere beforehand.

Less code = less chance of introducing bugs.

 

[arnelgp]

Less code = less chance of introducing bugs.

there is no harm on testing for .Bof and .Eof.
actually it is much safer when the recordset is ADODB.Recordset.

 

[ebs17]

CurrentDb.OpenRecordset will never create an ADODB recordset.

I'm very much in favor of timely testing so as not to have to control an application via error handling. Error-free code has something to offer and should be the programmer's first priority. You don't have to check for things that can't happen at all or are irrelevant at the point.

 

[arnelgp]

Error-free code

of course i agree, but i just scribble those code without testing?
if i had tested it, surely i will catch the error.
Solved!