Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 2 of 2

Thread: Importing a CSV file to Excel

  1. #1
    New Member
    Join Date
    Feb 2002
    Posts
    13
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I'm attempting to set up a query that will import a file that's comma delimited. I keep getting an error "Invalid String or Buffer Length" Any ideas on how to get this thing imported? The wizard pops up fine...The data source is fine. I can see the file I need to import, but when I click on it to move that file to the right hand box-I get the above error.

  2. #2
    Board Regular
    Join Date
    Aug 2002
    Posts
    106
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Here's some macro code that automates a CSV upload--

    Public Sub ImportTextFile(FName As String, Sep As String)

    Dim RowNdx As Integer
    Dim ColNdx As Integer
    Dim TempVal As Variant
    Dim WholeLine As String
    Dim Pos As Integer
    Dim NextPos As Integer
    Dim SaveColNdx As Integer

    Application.ScreenUpdating = False
    'On Error GoTo EndMacro:

    SaveColNdx = ActiveCell.Column
    RowNdx = ActiveCell.Row

    Open FName For Input Access Read As #1

    While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
    WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
    TempVal = Mid(WholeLine, Pos, NextPos - Pos)
    Cells(RowNdx, ColNdx).Value = TempVal
    Pos = NextPos + 1
    ColNdx = ColNdx + 1
    NextPos = InStr(Pos, WholeLine, Sep)
    Wend
    RowNdx = RowNdx + 1
    Wend

    EndMacro:
    On Error GoTo 0
    Application.ScreenUpdating = True
    Close #1

    End Sub

    Public Sub DoTheImport()
    Dim FName As Variant
    Dim Sep As String

    FName = Application.GetOpenFilename _
    (filefilter:="Text Files(*.txt),*.txt,All Files (*.*),*.*")
    If FName = False Then
    MsgBox "You didn't select a file"
    Exit Sub
    End If

    Sep = InputBox("Enter a single delimiter character.", _
    "Import Text File")
    If Sep = "" Then
    Sep = Chr(9)
    End If
    ImportTextFile CStr(FName), Sep
    End Sub

    Private Sub CommandButton1_Click()
    DoTheImport
    End Sub




    <font size="-1">Mark Henri</font>

    <font size="-2">Microsoft Tier 1 Excel Support Technician</font>

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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