How to append records to and Excel Spreadsheet.

[jake007]

I am looking to append records to an existing excel spreadsheet on a daily basis. I know how to create a new spreadsheet and write to it, but How would I go about appending rows to an existing spreadsheet? How do I know the next available row when I start the append processes?

Thanks,

Jake

 

[mmilan]

There are two methods you might consider when approaching this problem. The first would be to drive the Excel application itself using Automation, and from your question, I guess this is what you are trying to do.

The other option which I have used in the past however is to connect to the spreadsheet through ADO, which then lets you treat the sheet as a recordset - which means you can simply use the addnew method.

Hope that helps,

mmilan.

 

[Swi]

I would use mmilan's method of using a recordset but here it is in Excel if you want it:

Private Sub Command1_Click()
' Declare variables
Dim xlApp As Excel.Application
Dim xlWorkbook As Excel.Workbook
Dim xlWorksheet As Excel.Worksheet
Dim BlankRow As Long

' Set up objects
Set xlApp = New Excel.Application
Set xlWorkbook = xlApp.Workbooks.Open("C:\Testit.xls")
Set xlWorksheet = xlWorkbook.ActiveSheet
xlApp.Visible = False
xlApp.DisplayAlerts = False

' Determine last blank row
BlankRow = xlWorksheet.Cells.SpecialCells(xlCellTypeLastCell).Row + 1
xlWorksheet.Cells(BlankRow, 1).Value = "Cool, it worked!"

' Saves spreadsheet and destroys objects
xlWorkbook.Save
xlApp.Quit
Set xlWorksheet = Nothing
Set xlWorkbook = Nothing
Set xlApp = Nothing

' Prompts user when finished
MsgBox "Data Appended Successfully!", vbInformation
End Sub

Swi

 

[jake007]

Thanks mmilan and Swi, I will probably use Swi's example because it is something I can accomplish by end of day today, but would also like some info on connecting with ADO and treating the spreadsheet as a recordset. This seems much cleaner. Any more infor on this would be appreciated.

Thanks again,

Jake

 

[mmilan]

Well, I haven't got time to go into an explanation at the mo, but I'll post you an example of a function I use when converting a Excel spreadsheet into a CSV file. It basically get's the first sheet and then parties with that...

Private Sub Convert(sFile As String)

Dim objConn As ADODB.Connection
Dim objRs As ADODB.Recordset
Dim sWS As String
Dim objField As ADODB.Field
Dim sLine As String
Dim sItem As String
Dim bFirstItem As Boolean
Dim iFreeFile As Integer

Set objConn = New ADODB.Connection
objConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                   "Data Source=" + sFile + ";" & _
                   "Extended Properties=""Excel 8.0;HDR=YES"";"
Set objRs = objConn.OpenSchema(adSchemaTables)
If objRs.EOF Then Exit Sub
sWS = objRs!table_name
objRs.Close
objRs.Open "Select * from [" + sWS + "]", objConn, adOpenForwardOnly, adLockReadOnly

iFreeFile = FreeFile()
Open Left(sFile, Len(sFile) - 4) + ".csv" For Output As #iFreeFile

Do Until objRs.EOF
  sLine = ""
  bFirstItem = True
  For Each objField In objRs.Fields
    If IsNull(objField.Value) Then
      sItem = ""
    Else
      sItem = objField.Value
    End If

    'If Not IsNumeric(sItem) Then sItem = """" + sItem + """"
    If Not bFirstItem Then sLine = sLine + ","
    sLine = sLine + sItem
    bFirstItem = False
  Next objField

  Print #iFreeFile, sLine

  
  objRs.MoveNext

Loop
Label1.Caption = sFile
Label1.Refresh
Form1.Refresh

Close #iFreeFile
Set objRs = Nothing
Set objcon = Nothing

End Sub

Hope that helps you out.