• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 226
  • Last Modified:

Add Records To Existing Excel File!

Hi Experts.
I was able to add records to a new excel file but this is not what i want.
I want to add records to a existing excel file. It must be able to add to the same row all at the same time and not A1, B5, C10 which i did.
example:

A1    B1      C1
tel  fax     email

this is the code i done below:

Dim sql_str As String
Dim sql_str2 As String
Dim sql_str3 As String
Dim sql_str4 As String
Dim sql_str5 As String
Dim NashXl As Excel.Application


Private Sub Command1_Click()
NashXl.Workbooks.Add
NashXl.Range(Combo1.Text).Value = Text1.Text
NashXl.Range(Combo2.Text).Value = Text2.Text
NashXl.Range(Combo3.Text).Value = Text3.Text
NashXl.Range(Combo4.Text).Value = Text4.Text
NashXl.Range(Combo5.Text).Value = Text5.Text

NashXl.Visible = True
End Sub

Private Sub Command2_Click()
Unload Me

End Sub

Private Sub Form_Load()
Set NashXl = CreateObject("excel.application")
    Call OpenAccessDataBase("numDB.mdb", "\Database")
    sql_str = "Select Num from Records"
    sql_str2 = "Select Num2 from Records"
    sql_str3 = "Select Num3 from Records"
    sql_str4 = "Select Num4 from Records"
    sql_str5 = "Select Num5 from Records"

    Call OpenAccessRecordSet(sql_str)
    Do Until SrstObj.EOF

        Combo1.AddItem SrstObj("Num")
        SrstObj.MoveNext
    Loop
    Call CloseAccessRecordSet
   
    Call OpenAccessRecordSet(sql_str2)
    Do Until SrstObj.EOF

        Combo2.AddItem SrstObj("Num2")
        SrstObj.MoveNext
    Loop
    Call CloseAccessRecordSet
   
    Call OpenAccessRecordSet(sql_str3)
    Do Until SrstObj.EOF

        Combo3.AddItem SrstObj("Num3")
        SrstObj.MoveNext
    Loop
    Call CloseAccessRecordSet
   
    Call OpenAccessRecordSet(sql_str4)
    Do Until SrstObj.EOF

        Combo4.AddItem SrstObj("Num4")
        SrstObj.MoveNext
    Loop
    Call CloseAccessRecordSet
   
    Call OpenAccessRecordSet(sql_str5)
    Do Until SrstObj.EOF

        Combo5.AddItem SrstObj("Num5")
        SrstObj.MoveNext
    Loop
    Call CloseAccessRecordSet
   

End Sub

Private Sub Form_Unload(Cancel As Integer)
NashXl.Quit
End Sub
0
NightKids
Asked:
NightKids
  • 2
  • 2
1 Solution
 
leavinmateCommented:
It looks like that you are selecting the data from the access db and copying to sheet in a format similar to a DB Table.

A1     b1    c1
-----------------
tel    fax  email


Why not use an ADO RecordSet to query the worksheet and add records?

Private Sub cmdSample1_Click()
    'Open the ADO connection to the Excel workbook
    Set oConn = New ADODB.Connection
    oConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
               "Data Source=" & App.Path & "\Results\Orders1.xls;" & _
               "Extended Properties=""Excel 8.0;HDR=YES;"""

    'Open a connection to the Northwind database and retrieve the information
    'in OrderDetails table
    Dim oNWindConn As New ADODB.Connection, oOrdersRS As New ADODB.Recordset
    oNWindConn.Open "provider=microsoft.jet.oledb.4.0; data source=" & sNwind
                   
    oOrdersRS.Open "SELECT [Order Details].OrderID, Products.ProductName, " & _
                   "[Order Details].UnitPrice , [Order Details].Quantity, " & _
                   "[Order Details].Discount FROM Products INNER JOIN " & _
                   "[Order Details] ON Products.ProductID = " & _
                   "[Order Details].ProductID ORDER BY [Order Details].OrderID", _
                   oNWindConn, adOpenStatic
                   
    '**Note: The first "row" in the Orders_Table is hidden -- it contains dummy data that
    '        the OLE DB Provider uses to determine the data types for the table.
   
    'Add the data from the Order Details table in Northwind to the workbook
    Dim oRS As New ADODB.Recordset
    oRS.Open "Select * from Orders_Table", oConn, adOpenKeyset, adLockOptimistic
   
    Do While Not (oOrdersRS.EOF)
        oRS.AddNew
        For i = 0 To 4
            oRS.Fields(i).Value = oOrdersRS.Fields(i).Value
        Next
        oRS.Update
        oOrdersRS.MoveNext
    Loop
   
    'Close the recordset and connection to Northwind
    oOrdersRS.Close
    Set oOrdersRS = Nothing
    oNWindConn.Close
    Set oNWindConn = Nothing
   
    'Close the connection to the workbook
    oConn.Close
    Set oConn = Nothing
End Sub

This isn't my code.  I found this on the web and can't remember where I found it.

Use this as a template.
0
 
NightKidsAuthor Commented:
The code i done is wrong!
Just posting what i done!!
i want to add the records to a exisiting excel file

the output should be something like:
     A        B        C
1    tel      fax      email

if i continue adding records,it should be:
     A        B        C
1    tel      fax      email
2    tel_1    fax_1    email_1

it should not be:
     A        B        C
1    tel      
2             Fax
3
4                      email
0
 
stefriCommented:
Excel is using cells, so you will add cells by cells
To add to an existing excel sheet, get the last used row in the sheet with
dim lRow as long
lRw = theSheet.usedrange.rows.count
next available row will be lRw = lRw +1
your values will therefore be copied to

theSheet.cells(lRw,1) = aTelValue
theSheet.cells(lRw,2) = aFaxValue
theSheet.cells(lRw,3) = aEmailValue
lRw = lRw + 1 and so on

0
 
NightKidsAuthor Commented:
stefri
can u help me do a sample program!!!
i pass u what i done!!
u see what u can help me!!
i will increase the points to 80
can u pass me ur email!!!
0
 
stefriCommented:
Try this and adjust to your needs

Sub txt()
Dim xlsApp As Excel.Application
Dim xlsWbk As Excel.Workbook
Dim xlsFile As String
Dim xlsSheet As Excel.Worksheet

Dim sheetName As String
Dim nr As Long

xlsFile = "C:\Book1.xls"

sheetName = "Sheet1" ' or whatever name of the sheet to use
Set xlsApp = CreateObject("excel.application")
Set xlsWbk = xlsApp.Workbooks.Open(xlsFile)
Set xlsSheet = xlsWbk.Sheets(sheetName)
xlsSheet.Activate


nr = xlsSheet.UsedRange.Rows.Count

' set the code to access your db value here
set conn = openConn
sqlStr = .....
set rs = getRec(conn, sqlStr)

' lets say you will have three values to get
' tel, fax, email
' tel in col A, fax in col B, email in col C
nr = nr + 1

Do Until rs.EOF
    xlsSheet.Cells(1, nr).Value = rs("tel")
    xlsSheet.Cells(2, nr).Value = rs("fax")
    xlsSheet.Cells(3, nr).Value = rs("email")
    nr = nr + 1
    rs.movenext
Loop
'save the workbook
xlsWbk.Close True

Set xlsSheet = Nothing
Set xlsWbk = Nothing
Set xlsApp = Nothing
' clean your db stuff

End Sub

Good luck
stefri
PS: suggestion from leavinmate is more elegant than mine and certainly faster....
0

Featured Post

Become an Android App Developer

Ready to kick start your career in 2018? Learn how to build an Android app in January’s Course of the Month and open the door to new opportunities.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now