none
Access vba code to execute text to columns in Excel RRS feed

  • Question

  • Hi All,

    I have access database and i want to import excel file into it but before importing i need to do text to column in excel. As this excel file is extract of sharepoint site i have to write this code in access. I recorded a macro in excel file to do text to column and is working fine in xl module but when i am using this code in access module it throws error "Run-time error '1004: Method range of object_ global failed". Here is the code

    Private Sub SeperateData()
    Dim objXls As Excel.Application
    Dim MyBook As Excel.Workbook
    Dim MySheet As Excel.Worksheet
    Dim strfile As String
    Dim myrow As Range

    strfile = "C:\Documents and Settings\AAAA\Desktop\ABG"

    Set objXls = CreateObject("Excel.Application")

        objXls.Workbooks.Open ("" & strfile)
        objXls.Visible = False
    Set MyBook = objXls.Workbooks("ABG")
    Set MySheet = MyBook.Worksheets("Sheet1")
    MySheet.Activate
    With MySheet
    'objXls.Visible = False
    'Set mysheet = xlWb.Worksheets("Data")
    'mysheet.Activate
    objXls.Application.DisplayAlerts = False

    MySheet.Range("E2:E5997").Select

    ' Following macro in Bold is throwing an error

        Selection.TextToColumns Destination:=Range("F2"), DataType:=xlFixedWidth, _
            FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
    End With
    objXls.Application.DisplayAlerts = True

    MyBook.Saved = True
        MyBook.Close
        objXls.Application.Quit

    Set objXls = Nothing
    Set MyBook = Nothing
    Set MySheet = Nothing

    End Sub

     

    Wednesday, June 29, 2011 8:56 PM

All replies

  • 'Selection' isn't available via automation. Use a method of a Range objetc directly:

    MySheet.Range("E2:E5997").TextToColumns Destination:=Range("F2"), DataType:=xlFixedWidth, _
        FieldInfo:=Array(Array(0, 1), Array(2, 1)), TrailingMinusNumbers:=True
    



    Andrey V Artemyev | Saint-Petersburg, Russia
    Wednesday, June 29, 2011 9:07 PM
  • It throws same errror!
    Wednesday, June 29, 2011 9:26 PM