Results 1 to 5 of 5
  1. #1
    forrestgump is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    2

    Open CSV file from MS Access

    Hi All,



    I am trying to open a csv file from MS Access. I am having to open excel then import the information so i don't lose the leading zeros. I have tried the below code but it behaves strangely. It work alternate times i try it. First it works then the next time i try it i get error message 'Range of object Global_Failed'. Does anyone have any ideas? Any help greatly appreciated.

    Code:
    Private Sub Command41_Click()
     Dim xlApp As Excel.Application
        Dim xlWB As Excel.Workbook
        Set xlApp = New Excel.Application
        Set xlWB = xlApp.Workbooks.Add
        xlApp.Visible = True
        xlApp.Sheets("Sheet1").Select
     xlApp.Range("A5").Select
         xlApp.Sheets("Sheet1").Select
    xlApp.Range("A1").Select
        With xlApp.ActiveSheet.QueryTables.Add(Connection:= _
            "TEXT;C:\Users\elwelj01\Documents\004_Compensation.csv", Destination:=Range("$A$1"))
            .Name = "004_Compensation"
            .FieldNames = True
            .RowNumbers = False
            .FillAdjacentFormulas = False
            .PreserveFormatting = True
            .RefreshOnFileOpen = False
            .RefreshStyle = xlInsertDeleteCells
            .SavePassword = False
            .SaveData = True
            .AdjustColumnWidth = True
            .RefreshPeriod = 0
            .TextFilePromptOnRefresh = False
            .TextFilePlatform = 437
            .TextFileStartRow = 1
            .TextFileParseType = xlDelimited
            .TextFileTextQualifier = xlTextQualifierDoubleQuote
            .TextFileConsecutiveDelimiter = False
            .TextFileTabDelimiter = False
            .TextFileSemicolonDelimiter = False
            .TextFileCommaDelimiter = True
            .TextFileSpaceDelimiter = False
            .TextFileColumnDataTypes = Array(2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2)
            .TextFileTrailingMinusNumbers = True
            .Refresh BackgroundQuery:=False
        End With 
    End Sub


  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    you cannot have leading zeros in a number. You must make it a string.
    So if you have the data in access (linked CSV), make a query that converts it to string:
    CSTR(field)
    or
    put a single quote in front of the number:
    "'" & field

    There theres no reason to 'import' the csv to excel, just paste the query straight from access query as a recordset....\

    range("A1").copyfromrecordset rst

  3. #3
    forrestgump is offline Novice
    Windows 10 Access 2010 64bit
    Join Date
    Sep 2017
    Posts
    2
    I can import the csv file but i need to edit the file before import.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I recommend you step back and tell us in plain English
    -what you are trying to do
    -give us some ideas about your csv file ..what it represents and why you need to edit it

  5. #5
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    hence my solution

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 6
    Last Post: 09-14-2017, 07:31 AM
  2. Can't open Access file
    By rightstart in forum Access
    Replies: 5
    Last Post: 09-09-2014, 01:57 AM
  3. Replies: 2
    Last Post: 06-25-2014, 10:29 PM
  4. Replies: 4
    Last Post: 09-28-2010, 07:04 PM
  5. Replies: 1
    Last Post: 09-27-2010, 10:10 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums