Results 1 to 10 of 10
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import CSV file Macro (2003)

    I have a macro that imports a csv file into Excel (see attached). I am getting a run time error 1004 - Application-defined or Object-defined error. The part that is getting the error is highlighted on the attachment. Can someone tell me what is wrong. I think it worked before but it has been a while since I ran it. Thanks for any help you can provide
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import CSV file Macro (2003)

    Either use
    <code>
    .Range("A2").Resize(lLimit - 1, 1).Value = sArray
    </code>
    or
    <code>
    .Range("A1").Resize(lLimit, 1).Value = sArray
    </code>
    Since lLimit = 65536 (the number of rows in a worksheet), you cannot expand from A2 to lLimit rows.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Posts
    1,119
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import CSV file Macro (2003)

    Thanks, I made the adjustment and it works.

  4. #4
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import CSV file Macro (2003)

    Dear Hans,

    I used this code to import a.csv file and it works beautifully Except that it puts all the data in two columns. How do i modify the code so that it puts every entry in a separate column?

    I would really, really appreciate your help.

    Regards,

    paul

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import CSV file Macro (2003)

    Do you really need the macro? As long as the .csv file contains fewer than 65536 lines, Excel can open it directly, either interactively or using VBA. The macro from LindaR's post is intended to handle files with more than 65536 lines.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import CSV file Macro (2003)

    Excel is very wayward when it comes to handling CSV files in VBA - it stubbornly uses US settings even if you tell it explicitly to use European settings. The way around is to change the extension to .txt instead of .csv. It then becomes very easy to import the file.
    <code>
    Sub Importeer_CSV()
    Const strFile = "C:TempED070912"
    ' Rename .csv to .txt
    Name strFile & ".csv" As strFile & ".txt"
    ' Open in Excel
    Workbooks.OpenText Filename:=strFile & ".txt", _
    DataType:=xlDelimited, SemiColon:=True, Comma:=False
    End Sub
    </code>
    That's it!

  7. #7
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import CSV file Macro (2003)

    Hans,

    You are amazing. Thank you a million times.

    Paul

  8. #8
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import CSV file Macro (2003)

    Edited by HansV to remove potentially sensitive information

    Hans,

    We need to automatically print letters with dosages of medicine. Each day we import a csv-file and mail merge in word. Iwant to use the macro so that people could do this with one click. (build it in to a word document) I've changed the macro a bit to fit our needs but it doesn't seem to put all the data in separate columns.
    Here's the code as I have it now:
    Sub Importeer_CSV()
    Dim wks As Worksheet
    Dim sPathFilename As String
    Dim lRow As Long
    Dim lLimit As Long
    Dim sLine As String
    Dim sArray() As String

    sPathFilename = "C:Temp" & "ED070912.csv"
    lLimit = 500
    ReDim sArray(2 To lLimit, 0)
    lRow = 2

    Open sPathFilename For Input As #1
    Do While Not EOF(1)
    Line Input #1, sLine
    sArray(lRow, 0) = sLine
    lRow = lRow + 1
    Loop
    Set wks = Worksheets.Add
    With wks
    .Range("A1").Resize(lLimit, 1).Value = sArray
    .Columns("a:a").TextToColumns DataType:=xlDelimited, TextQualifier:=xlTextQualifierDoubleQuote, _
    Comma:=True
    End With
    Close #1
    Set wks = Nothing
    End Sub

    Here is a part of the file:

    removed

    Again I would be VERY GRATEFUL for your help.

    PAul

  9. #9
    Lounger
    Join Date
    Jan 2006
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import CSV file Macro (2003)

    Hans,

    I hate to get back to you, your code works of course but i need to specify the file through a fileopen dialog box. (people need to browse to the correct file) So i cannot use the first line. I've tried it with the 'name' argument but keep getting an 'invalid qualifier' error. I used this code:
    Set Dialoog = Dialogs(wdDialogFileOpen)
    Dialoog.Show
    StrBestand = Dialoog.Name

    Apparently 'name' is not the actual object but purely a name. Is it al all possible to let the user browse to a file and then run your code?

    Thanks again,

    Paul

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import CSV file Macro (2003)

    Here is a version that prompts the user for a file.

    Sub Importeer_CSV()
    Dim varCsv As Variant
    Dim strTxt As String
    varCsv = Application.GetOpenFilename("CSV files(*.csv),*.csv")
    If VarType(varCsv) = vbBoolean Then
    ' User canceled - get out
    Exit Sub
    Else
    strTxt = Replace(varCsv, ".csv", ".txt")
    ' Rename .csv to .txt
    Name varCsv As strTxt
    ' Open in Excel
    Workbooks.OpenText Filename:=strTxt, _
    DataType:=xlDelimited, SemiColon:=True, Comma:=False
    End If
    End Sub

Posting Permissions

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