Results 1 to 2 of 2
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Add a row using vba to Excel (excel xp)

    I have 2 workbooks, test.xls and test2.xls. Test is attached. I need to take in a new file every week that will come as a comma delimited file. This file will have one row in it. I have to add this row to the end of test.xls. (at row 16 in this case).

    Since Col C & D have formulas and Col D is linked to test2.xls, i'm not sure how to do it. If i copy the new row on top of row 16 it will wipe out the formulas. The new comma delimited file will really only have one number, for instance it might have 6000 in it. I need 6000 to go in Col B16 and then the formulas will update and the link will update and i need to add a 16 to COL 1.

    Thank you for any help on this.
    Attached Files Attached Files

  2. #2
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Add a row using vba to Excel (excel xp)

    The following VBA code will display the standard File Open dialog box to allow you to select the CSV file containing the new data. It will then open the .csv file and copy the data that it puts into cell A1 in the resulting workbook into the cell after the last data in column B. If the CSV file puts the data in some other cell than A1, the code will have to be adjusted.

    <pre>Public Sub AddData()
    Dim wbNew As Workbook
    Dim lNextRow As Long
    Dim vFileName As Variant
    lNextRow = Worksheets("Sheet1").Range("B65536").End(xlUp).Row
    vFileName = Application.GetOpenFilename()
    If vFileName = False Then Exit Sub
    Set wbNew = Workbooks.Open(vFileName)
    ThisWorkbook.Worksheets("Sheet1").Range("B1").Offs et(lNextRow, 0).Value = _
    wbNew.Worksheets(1).Range("A1").Value
    wbNew.Close
    Set wbNew = Nothing
    End Sub
    </pre>

    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •