Results 1 to 3 of 3
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Edit (delete columns) Excel from Access (Access 2000)

    I want to edit an Excel workbook using VBA in order to prep it for import to Access. The information that I need is always in the same column. I need to delete all but column M, delete the top 3 rows, and rename the column heading. I need to accomplish this entirely from VBA in Access.

    Can anyone give me some pointers on where to learn how to connect to the Excel file, delete columns, sort, rename columns, etc. from VBA?

    Thanks,

    Randy

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Edit (delete columns) Excel from Access (Access 2000)

    One approach is to create a named range in Excel, and then import only that range. Otherwise you will have to lots of gymnastics in order to get the information you are after, and it would have to be done using Automation. If you haven't had experience with that, it's not a trivial task. You could also link to the specific information you want in column M, and never actually pull it into Access. If you want to pursue the Automation approach, the MS knowledge base has some articles on it - search for Excel and Automation.

    Another strategy would be to import the entire workbook into a table and then manipulate the data in Access. That a bit friendlier from a programming perspective, but would involve working with the TableDef collection and figuring out what the first three records were and deleting them.
    Wendell

  3. #3
    3 Star Lounger
    Join Date
    Jun 2001
    Location
    Maidstone, Kent, England
    Posts
    398
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Edit (delete columns) Excel from Access (Access 2000)

    Here is some code that may help you.
    You will need to make sure that you set the reference to Excel under Tools>references... in any module.
    Oviously you will need to adjust paths/names to suit your needs.

    <pre>Sub prepSheet()
    Dim oXL As New Excel.Application
    Dim oXB As New Excel.Workbook

    'The Excel Objects are assigned in this next bit
    oXL.Workbooks.Open ("CataTestSheet.xls")
    Set oXB = oXL.Workbooks("TestSheet.xls")

    With oXL.Worksheets("Sheet2")
    .Columns("A:l").Delete Shift:=xlToLeft
    .Columns("B:iv").Delete Shift:=xlToLeft
    .Rows("1:3").Delete Shift:=xlUp
    .Range("A1") = "New Header"
    End With
    oXB.Save
    oXL.Quit
    Set oXB = Nothing
    Set oXL = Nothing
    End Sub

    </pre>


    HTH

    Peter

Posting Permissions

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