Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    88

    Unanswered: vba open delimited text file with Excel

    I have a text file with 2 mixed delimiters, space and tab.
    I would like to use access vba to open the delimited text file using excel and then save the excel file. I would like to have a function to pass the parameters so it can be reused. The parameters are: "text file name", delimiter1, delimiter2, and the"final saved excel file name". Thank you very much for help.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Is it for Access, for Excel, or both?

    What do you have so far?
    Have a nice day!

  3. #3
    Join Date
    Aug 2007
    Posts
    88
    I don't have anything yet since I can not find any post regarding 2 delimiters in a text file. The only thing I know is I should use split function to read in text file and open in Excel.

    I need to be in Access 2002 program, write a Access VBA code to open the text file in Excel and save the excel sheet.

    Thanks!

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Reading a test file is VBA is easy:
    Code:
    Function ReadTextFile(ByVal FileName As String)
    
        Dim intHandle As Integer
        Dim strLine As String
        
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
        Loop
        Close #intHandle
        
        
    End Function
    To solve the two delimiters problem, the easiest solution (not the fastest) would probably consist in using the Replace funtion first (to replace one of the delimiter by the other, then use the Split function to seperate the different elements. We now have:

    Code:
    ---
        Line Input #intHandle, strLine
        strline=Replace(strLine, Delim1, Delim2)
        varElements = Split(strLine, Delim2)
    ---
    You can use Automation to send the values to Excel, so setting everything together we come to:
    Code:
    Function ProcessTextFile(ByVal TextFileName As String, ByVal Delim1 As String, ByVal Delim2 As String, ByVal ExcelFileName As String)
    
        Dim appExcel As Excel.Application
        Dim intHandle As Integer
        Dim strLine As String
        Dim varElements As Variant
        Dim intCol As Integer
        Dim intRow As Integer
        
        Set appExcel = New Excel.Application
        With appExcel
            .Visible = True
            .Workbooks.Add
            intHandle = FreeFile
            Open TextFileName For Input As #intHandle
            Do Until EOF(intHandle)
                intRow = intRow + 1
                Line Input #intHandle, strLine
                strLine = Replace(strLine, Delim1, Delim2)
                varElements = Split(strLine, Delim2)
                For intCol = 0 To UBound(varElements)
                    .ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
                    .Selection = varElements(intCol)
                Next intCol
            Loop
            Close #intHandle
            .ActiveWorkbook.SaveAs FileName:=ExcelFileName
            .Quit
        End With
        Set appExcel = Nothing
        
    End Function
    You could also store the imported data into a table and export that table to Excel using the TransferSpreadsheet method of the DoCmd object.

    What I do not understand is why you need Access to perform this operation. You could use the same functionalities directly in Excel.
    Have a nice day!

  5. #5
    Join Date
    Aug 2007
    Posts
    88
    Quote Originally Posted by Sinndho View Post
    Reading a test file is VBA is easy:
    Code:
    Function ReadTextFile(ByVal FileName As String)
    
        Dim intHandle As Integer
        Dim strLine As String
        
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
        Loop
        Close #intHandle
        
        
    End Function

    To solve the two delimiters problem, the easiest solution (not the fastest) would probably consist in using the Replace funtion first (to replace one of the delimiter by the other, then use the Split function to seperate the different elements. We now have:

    Code:
    ---
        Line Input #intHandle, strLine
        strline=Replace(strLine, Delim1, Delim2)
        varElements = Split(strLine, Delim2)
    ---
    You can use Automation to send the values to Excel, so setting everything together we come to:
    Code:
    Function ProcessTextFile(ByVal TextFileName As String, ByVal Delim1 As String, ByVal Delim2 As String, ByVal ExcelFileName As String)
    
        Dim appExcel As Excel.Application
        Dim intHandle As Integer
        Dim strLine As String
        Dim varElements As Variant
        Dim intCol As Integer
        Dim intRow As Integer
        
        Set appExcel = New Excel.Application
        With appExcel
            .Visible = True
            .Workbooks.Add
            intHandle = FreeFile
            Open TextFileName For Input As #intHandle
            Do Until EOF(intHandle)
                intRow = intRow + 1
                Line Input #intHandle, strLine
                strLine = Replace(strLine, Delim1, Delim2)
                varElements = Split(strLine, Delim2)
                For intCol = 0 To UBound(varElements)
                    .ActiveSheet.Range(Chr(65 + intCol) & CStr(intRow)).Select
                    .Selection = varElements(intCol)
                Next intCol
            Loop
            Close #intHandle
            .ActiveWorkbook.SaveAs FileName:=ExcelFileName
            .Quit
        End With
        Set appExcel = Nothing
        
    End Function
    You could also store the imported data into a table and export that table to Excel using the TransferSpreadsheet method of the DoCmd object.

    What I do not understand is why you need Access to perform this operation. You could use the same functionalities directly in Excel.

    Hi Sinndho,

    Thank you so much!
    Wow, it's so cool to see the excel open and all the text "run" line by line.
    It works perfectly!
    The reasons that I have to do this in access are:
    1. It's part of a function I need to achieve in the access database application.
    2. Access is the only thing I know and I only know a little bit of it. I don't know much about Excel. Can Excel work like access to save data values like access tables?

    Thanks again!

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Posting Permissions

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