Clearing Excel files with no records

[Skip Bisconer]

I have a function with 13 sets of the following code to clear Excel files to prepare for the next period. My problem comes when the linked file has no records. This process destroys the column headers of an empty file. Could some one advise me on getting past the do loop when there is no records in the spreadsheet.

DoCmd.SetWarnings False
DoCmd.OpenQuery "PostToCompletedMonthlyTransaction12085", acViewNormal
DoCmd.OpenQuery "PostToHistoryCurrentCharges12085", acViewNormal
DoCmd.SetWarnings True
Set xlWB = objXL.Workbooks.Open("C:\Amex\CurrentChargesBriare.xls")
Set xlWS = xlWB.Worksheets("CurrentCharges")
xlWB.Worksheets("CurrentCharges").Unprotect Password:=("1234")
Set db = CurrentDb
Set rs = db.OpenRecordset("CurrentCharges12085", , dbOpenDynamic)
If rs.BOF And rs.EOF Then
MsgBox "There are no records for Briare" [COLOR=red]'My problem is here I need what comes after the msgbos to get past Else.[/COLOR]
 
Else
rs.MoveFirst
i = 2
Do Until rs.EOF
  With xlWS
  ' assign records to specific cells
  .Range("A" & i).Value = ""
  .Range("B" & i).Value = ""
  .Range("C" & i).Value = ""
  .Range("D" & i).Value = ""
  .Range("E" & i).Value = ""
  .Range("F" & i).Value = ""
  .Range("G" & i).Value = ""
  .Range("H" & i).Value = ""
  .Range("I" & i).Value = ""
  .Range("J" & i).Value = ""
  .Range("K" & i).Value = ""
  .Range("L" & i).Value = ""
  .Range("M" & i).Value = ""
  .Range("N" & i).Value = ""
 
  End With
i = i + 1
rs.MoveNext
Loop
End If
xlWB.Worksheets("CurrentCharges").Protect Password:=("1234")
objXL.ActiveWorkbook.Save
objXL.Application.Quit
rs.Close
db.Close

 

[MrQ]

Hi Skip,
I may be misunderstanding but could you not insert a movenext after the movefirst?

Q

 

[DCrake]

What error are you getting?
You code seems alright but you could move your rs checking above your excel stuff and state that if rs.eof then exit sub.

 

[Skip Bisconer]

Thanks for responding to my problem. Unfortunately this is one of 14 strings of code and if I insert Exit Sub it does so and bypasses all the following code. I may have to breake these up into individual subs. I was trying to be slicker about it and save me some typing but maybe thats my only choice. The code works really great except when there are not records in the linked file. If I made seperate subs I could exit that sub in the if statement and move to the next function. I just thougt maybe there was a statement that in the Then part that could get me past the do loop and get on with the rest of the code. Maybe something like GoTo. I am going to mess with it for a while longer before I break it up. Thanks for you suggestions.

 

[Skip Bisconer]

GoTo didn't solved the problem. I am going to break them up into seperate modules.

 

[Skip Bisconer]

I ended up breaking up the module and using
Code:
If rs.BOF and rs.EOF Then
MsgBox "This record has no lines"
Exit Sub

Else
rs.MoveFirst

......
Loop
End If