INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Excel export is over writing spreadsheet

Excel export is over writing spreadsheet

(OP)
I am trying to reuse some previous code but it seems like I am missing something...

The following code dumps the contents of table CleanPC_tbl into a spreadsheet and
I would like this to be an append but the spreadsheet keeps getting over written.
What am I missing?

Thanks

CODE

Set objXL = CreateObject("Excel.Application")
    Set db = CurrentDb
    Set rs1 = db.OpenRecordset("CleanPC_tbl", dbOpenSnapshot)
    If rs1.RecordCount > 0 Then      ' if this is an empty table then don't bother...
       Set objXL = CreateObject("Excel.Application")
       Set db = CurrentDb
      
       With objXL
         .Visible = True
          Set fso = CreateObject("Scripting.FileSystemObject")
        
           objXL.DisplayAlerts = False  'Remove prompt to save file
           If (fso.FileExists(Out_File)) Then
             Set objWkb = .Workbooks.Open(Out_File)
 '          End If
        Else
           objXL.DisplayAlerts = False  'Remove prompt to save file
           Set objWkb = .Workbooks.Add
             With objWkb
             .Worksheets(1).Name = "PCs on Order"
             .SaveAs Out_File, 51
             End With
'             tempName = rs1!Date
             objXL.DisplayAlerts = True     'Open after report is completes
         End If
       
       On Error Resume Next
         Set objSht = objWkb.Worksheets("PCs on Order")         'RSP
'         If tempSIMmode = True Then        ' If this is set to simulation mode set then add a title to the start of the spreadsheet
'            For Each fld In rs1.Fields
'               objSht.Cells(1, iCol + 1).Value = fld.Name
'               iCol = iCol + 1
'            Next
'         End If
         objWkb.Worksheets("RSP").Activate
         lngLastRow = objSht.Cells.Find(What:="*", _
           After:=objSht.Range("A1"), _
           LookAt:=2, _
           LookIn:=-4123, _
           SearchOrder:=1, _
           SearchDirection:=2, _
           MatchCase:=False).Row
        End With
        lngLastRow = lngLastRow + 1
        With objSht
         .Range("A" & lngLastRow).CopyFromRecordset rs1
          With .Rows(lngLastRow & ":" & lngLastRow + rs1.RecordCount)
           .Font.Bold = False
'          .HorizontalAlignment = -4108
           .Font.Color = vbBlack
           .Font.Name = "Calibri"
           .Font.Size = 11
          End With
'            With .Columns("A:A").NumberFormat = "mmmm dd, yyyy"
'            End With
        End With
        strSql = "Sales Order " & tempOrder & "has been added to the spreadsheet"
        reply = MsgBox(strSql, vbOKOnly, "Process Complete")
    End If
    Set rs1 = Nothing
    Set objSht = Nothing
    
   DoCmd.SetWarnings True     ' allow all update table qry messages
   objWkb.Save
   objWkb.Close
   objXL.Quit
   
   Set objXL = Nothing
   Set rs1 = Nothing 

RE: Excel export is over writing spreadsheet

When you step thru your code, and you already have some data in your worksheet that you want to keep...

What is the value of lngLastRow at the end of this piece of code:

CODE

lngLastRow = objSht.Cells.Find(What:="*", _
  After:=objSht.Range("A1"), _
  LookAt:=2, _
  LookIn:=-4123, _
  SearchOrder:=1, _
  SearchDirection:=2, _
  MatchCase:=False).Row
End With
lngLastRow = lngLastRow + 1 

Does it have a value of the empty (last) Row where you want to add your new data?

Have fun.

---- Andy

There is a great need for a sarcasm font.

RE: Excel export is over writing spreadsheet

(OP)

Quote (When you step thru your code, and you already have some data in your worksheet that you want to keep... What is the value of lngLastRow at the end of this piece of code:)



Thanks Andy

lngLastRow = 7 which is the first blank row after the contents in the spreadsheet where I want the append to start.

however, lngLastRow = lngLastRow + 1 = FALSE which is probably why it is failing.

UPDATE- My bad, the false is sown in the watch window for lngLastRow + 1 and there was a capy of the spreadsheet open

RE: Excel export is over writing spreadsheet

If the lngLastRow = 7, your code pretty much does this:

CODE

With objSht
  .Range("A7").CopyFromRecordset rs1
... 

So the data from the recordset rs1 is displayed (copied) starting in cell A7. Right?

Have fun.

---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close