Solved Exporting Error If fist query has no records... (1 Viewer)

[Number11]

I have this code that creates a report combining two separate queries into an excel template with two sheets, this runs perfect, until say no records are in the first query, throughs out an error.. how to i tell it to continue onto the next pls?

Dim rst1
Dim vFile1
vFile1 = "Template_Location.xlsx"
Set rst1 = CurrentDb.OpenRecordset("Data_1")
rst1.MoveLast
With XL
.Visible = False
.Workbooks.Open vFile1
.Sheets("New").Select
.Range("A2").Select
.ActiveCell.CopyFromRecordset rst1
.ActiveWorkbook.SaveAs filename:=("C:\Local-Saved_Location.xlsx")
.ActiveWorkbook.Close

Dim rst2
Dim vFile2
vFile2 = "C:\Local-Saved_Location.xlsx"
Set rst2 = CurrentDb.OpenRecordset("Data_2")
rst2.MoveLast
With XL
.Visible = False
.Workbooks.Open vFile2
.Sheets("Update").Select
.Range("A2").Select
.ActiveCell.CopyFromRecordset rst2
.ActiveWorkbook.SaveAs filename:=("C:\Report Location.xlsx")
.ActiveWorkbook.Close
.Application.Quit

 

[theDBguy]

Maybe do something like this?

...
Set rst1 = CurrentDb.OpenRecordset("Data_1")
rst1.MoveLast
If Not (rst1.BOF AND rst1.EOF) Then
    With XL
        ...
    End With
End If
...

 

[Number11]

Maybe do something like this?

...
Set rst1 = CurrentDb.OpenRecordset("Data_1")
rst1.MoveLast
If Not (rst1.BOF AND rst1.EOF) Then
    With XL
        ...
    End With
End If
...

No it didnt work same error

 

[Gasman]

You do not move to a record unless you know you are at a record?
So test for EOF first before trying anything.

 

[MajP]

I doubt that is the real code. There is no end with so that should not compile. Can you post your updated code with the EOF/BOF check? I expect something like.

Set rst1 = CurrentDb.OpenRecordset("Data_1")
if NOT (Rst1.EOF and RSt1.BOF) then
  rst1.MoveLast
  With XL
    .Visible = False
    .Workbooks.Open vFile1
    .Sheets("New").Select
    .Range("A2").Select
   .ActiveCell.CopyFromRecordset rst1
   .ActiveWorkbook.SaveAs filename:=("C:\Local-Saved_Location.xlsx")
   .ActiveWorkbook.Close
  end with
end if

 

[Eugene-LS]

this runs perfect, until say no records are in the first query

One more solution:

' ...
    Set rst1 = CurrentDb.OpenRecordset("Data_1")
    If rst1.RecordCount = 0 Then
        MsgBox "There are no records to export!", vbExclamation, "No entries"
        rst1.Close
        Set rst1 = Nothing
        Exit Sub
    End If
' ...

 

[Number11]

I doubt that is the real code. There is no end with so that should not compile. Can you post your updated code with the EOF/BOF check? I expect something like.

Set rst1 = CurrentDb.OpenRecordset("Data_1")
if NOT (Rst1.EOF and RSt1.BOF) then
  rst1.MoveLast
  With XL
    .Visible = False
    .Workbooks.Open vFile1
    .Sheets("New").Select
    .Range("A2").Select
   .ActiveCell.CopyFromRecordset rst1
   .ActiveWorkbook.SaveAs filename:=("C:\Local-Saved_Location.xlsx")
   .ActiveWorkbook.Close
  end with
end if

Private Sub Command25_Click()


Dim XL As Excel.Application
Set XL = CreateObject("excel.application")

Dim rst1
Dim vFile1

vFile1 = "Template_Location.xlsx"

Set rst1 = CurrentDb.OpenRecordset("Data_1")
rst1.MoveLast
If Not (rst1.BOF And rst1.EOF) Then
With XL
   .Visible = False
   .Workbooks.Open vFile1
   .Sheets("New").Select
   .Range("A2").Select
   .ActiveCell.CopyFromRecordset rst1
   .ActiveWorkbook.SaveAs filename:=("C:\Local-Saved_Location.xlsx")
   .ActiveWorkbook.Close
End With
End If

Dim rst2
Dim vFile2

vFile2 = "C:\Local-Saved_Location.xlsx"

Set rst2 = CurrentDb.OpenRecordset("Data_2")
rst2.MoveLast
If Not (rst2.BOF And rst2.EOF) Then
With XL
   .Visible = False
   .Workbooks.Open vFile2
   .Sheets("Update").Select
   .Range("A2").Select
   .ActiveCell.CopyFromRecordset rst2
   .ActiveWorkbook.SaveAs filename:=("C:\Report Location.xlsx")
   .ActiveWorkbook.Close
   .Application.Quit
End With
End If


End Sub

 

[theDBguy]

No it didnt work same error

Try to adjust the code as @MajP had it, by putting the MoveLast line inside the If/Then block.

 

[Number11]

Try to adjust the code as @MajP had it, by putting the MoveLast line inside the If/Then block.

Thanks working now

 

[MajP]

I left the movelast, but I do not think there is any reason or need for moving to the last record if one exists.

 

[Eugene-LS]

Code:

The same code with less strings:

Spoiler: Code:

Private Sub Command25_Click()
' ... procedure description here  ...
'---------------------------------------------------------------------------------------------------
Dim XL As Object
Dim rst
Dim vFile
On Error GoTo Command25_Click_Err

    vFile = "Template_Location.xlsx"
    Set XL = CreateObject("Excel.Application")

    Set rst = CurrentDb.OpenRecordset("Data_1")
    If Not (rst.BOF And rst.EOF) Then
        With XL
           .Visible = False
           .Workbooks.Open vFile
           .Sheets("New").Select
           .Range("A2").Select
           .ActiveCell.CopyFromRecordset rst
           .ActiveWorkbook.SaveAs "C:\Local-Saved_Location.xlsx"
        End With
    End If
    rst.Close
   
    Set rst = CurrentDb.OpenRecordset("Data_2")
    If Not (rst.BOF And rst.EOF) Then
        With XL
           .Sheets("Update").Select
           .Range("A2").Select
           .ActiveCell.CopyFromRecordset rst
           .ActiveWorkbook.SaveAs "C:\Report Location.xlsx"
           .ActiveWorkbook.Close
           .Application.Quit
        End With
    End If


Command25_Click_End:
    On Error Resume Next
    Set XL = Nothing
    rst.Close
    Set rst = Nothing
    Err.Clear
    Exit Sub

Command25_Click_Err:
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in Sub : " & _
           "Command25_Click - mod00Test.", vbCritical, "Error!"
    Err.Clear
    Resume Command25_Click_End
End Sub

 

[theDBguy]

Thanks working now

Glad to hear you got it sorted out. Good luck with your project.