?
Solved

Add Records To Existing Excel File!

Posted on 2003-03-26
5
Medium Priority
?
224 Views
Last Modified: 2006-11-17
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
Comment
Question by:NightKids
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 1

Expert Comment

by:leavinmate
ID: 8215152
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
 

Author Comment

by:NightKids
ID: 8215329
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
 
LVL 13

Expert Comment

by:stefri
ID: 8216571
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
 

Author Comment

by:NightKids
ID: 8216865
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
 
LVL 13

Accepted Solution

by:
stefri earned 320 total points
ID: 8219820
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

Want to be a Web Developer? Get Certified Today!

Enroll in the Certified Web Development Professional course package to learn HTML, Javascript, and PHP. Build a solid foundation to work toward your dream job!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
Article by: Martin
Here are a few simple, working, games that you can use as-is or as the basis for your own games. Tic-Tac-Toe This is one of the simplest of all games.   The game allows for a choice of who goes first and keeps track of the number of wins for…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…
Suggested Courses
Course of the Month10 days, 18 hours left to enroll

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question