Results 1 to 4 of 4

Thread: [RESOLVED] Importing Delimited Text Files to Excel with .NET

  1. #1

    Thread Starter
    Junior Member Dawg's Avatar
    Join Date
    Nov 2009
    Location
    Portland Or, USA
    Posts
    26

    Resolved [RESOLVED] Importing Delimited Text Files to Excel with .NET

    I need to dump a bunch of delimited data to Excel. In VB6 days, I would save the data to a text file, then use ExcelApp.ActiveSheet.QueryTables.Add (blah blah blah...) to import the data to Excel (I got this code from recording a macro while manually importing a text file).

    In VB.NET (2005), QueryTables does not appear to be an option for ActiveSheet.

    I could take the long path and manually parse thru the data writing to individual cells - however if I could just import it, it would be much faster.

    Part of the issue is not being able to record a macro in Excel in .NET format...

    So my primary question is, how do I import a delimited text file in to Excel without having to manually parse the data into individual cells.

    Secondary question is any tips for not being able to have ready made examples (in VB .NET format) by recording a macro.

    p.s. If this is a redundant question I apologize. I did a moderate amount of searching and didn't find any answers.

  2. #2
    PowerPoster 2.0 Negative0's Avatar
    Join Date
    Jun 2000
    Location
    Southeastern MI
    Posts
    4,367

    Re: Importing Delimited Text Files to Excel with .NET

    Quote Originally Posted by Dawg View Post
    In VB.NET (2005), QueryTables does not appear to be an option for ActiveSheet.
    Sure it does. I think the problem is that the Application.ActiveSheet returns an object, so you need to cast it to a worksheet to get the intellisense to work for you:

    Code:
            Dim x As New Excel.Application
            Dim s As Excel.Worksheet
    
            s = DirectCast(x.ActiveSheet, Excel.Worksheet)
            s.QueryTables.Add(blahblahblah)

  3. #3

    Thread Starter
    Junior Member Dawg's Avatar
    Join Date
    Nov 2009
    Location
    Portland Or, USA
    Posts
    26

    Thumbs up Re: Importing Delimited Text Files to Excel with .NET

    Thank You! You answered questions much bigger than the one I asked.

  4. #4
    New Member
    Join Date
    Nov 2009
    Posts
    1

    Re: [RESOLVED] Importing Delimited Text Files to Excel with .NET

    Hi - I'm trying to do a similar thing in my code and although there are no errors, the imported data does not appear. Can I ask what your QueryTables.Add parameters are? This is the code I'm trying to use:

    Dim objApp As New Excel.Application
    Dim objBook As Excel._Workbook
    Dim objBooks As Excel.Workbooks
    Dim objSheets As Excel.Sheets
    Dim objSheet As Excel.Worksheet
    Dim range As Excel.Range

    ' Create a new instance of Excel and start a new workbook.
    objBooks = objApp.Workbooks
    objBook = objBooks.Add
    objSheets = objBook.Worksheets
    objSheet = DirectCast(objSheets(1), Excel.Worksheet)
    range = objSheets(1).Range("A1", Reflection.Missing.Value)

    With objSheet.QueryTables.Add(Connection:="TEXT;C:\Dump\LAB_Tests\TestFile.csv", Destination:=range)
    .Name = "Test"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = Excel.XlCellInsertionMode.xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 1252
    .TextFileStartRow = 1
    .TextFileParseType = Excel.XlTextParsingType.xlDelimited
    .TextFileTextQualifier = Excel.XlTextQualifier.xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = False
    .TextFileSpaceDelimiter = False
    .TextFileOtherDelimiter = ""


    End With


    'Return control of Excel to the user.
    objApp.Visible = True
    objApp.UserControl = True

    'Clean up a little.
    range = Nothing
    objSheet = Nothing
    objSheets = Nothing
    objBooks = Nothing

Posting Permissions

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



Click Here to Expand Forum to Full Width