export data from file to excel

export data from file to excel

• davidw

  Thanks,

  I ended up using the following code and it worked great:

  Dim oExcel As Object

  oExcel = CreateObject("Excel.Application")

  'Open the text file and save it in the Excel workbook format.

  oExcel.Workbooks.OpenText("K:\FinalEffort", , DataType:=Excel.XlTextParsingType.xlDelimited, TextQualifier:=Excel.XlTextQualifier.xlTextQualifierNone, Comma:=True) 'xlTextQualifierNone=-4142

  oExcel.ActiveWorkbook.SaveAs("K:\FinalEffort", -4143) 'xlWorkbookNormal = -4143

  'Quit Excel.

  oExcel.Quit()

  oExcel = Nothing

  GC.Collect()

  
  

• mleary

  str.replace requires the object to be a number, is there any other quick and easy way to replace the commas with tabs

  My variation on the suggested method writes an entire row of data into one Excel cell.  Any ideas on how to place each csv value into an individual cell   Here is the code:

   intFileNum1 = FreeFile()

  FileOpen(intFileNum1, "K:\testCatch", OpenMode.Input)

  Dim Table6 As DataTable = EffortDataset.Tables.Add("Table6")

  Table6.Columns.Add(New DataColumn("YEAR", GetType(String)))

  Table6.Columns.Add(New DataColumn("BIGSIX", GetType(String)))

  Table6.Columns.Add(New DataColumn("STAT_WEEK", GetType(String)))

  Table6.Columns.Add(New DataColumn("CHIN DAYS", GetType(Integer)))

  Table6.Columns.Add(New DataColumn("CNR DAYS", GetType(Integer)))

  Table6.Columns.Add(New DataColumn("HAND_CHINOOK", GetType(Integer)))

  Table6.Columns.Add(New DataColumn("HAND_COHO", GetType(Double)))

  Table6.Columns.Add(New DataColumn("HAND_LANDINGS", GetType(String)))

  Table6.Columns.Add(New DataColumn("HAND_BOATS", GetType(Double)))

  Table6.Columns.Add(New DataColumn("HAND_BOATDAYS", GetType(Double)))

  Table6.Columns.Add(New DataColumn("PWR_CHINOOK", GetType(Double)))

  Table6.Columns.Add(New DataColumn("PWR_COHO", GetType(Integer)))

  Table6.Columns.Add(New DataColumn("PWR_LANDINGS", GetType(Double)))

  Table6.Columns.Add(New DataColumn("PWR_BOATS", GetType(Double)))

  Table6.Columns.Add(New DataColumn("PWR_BOATDAYS", GetType(Double)))

  Do While Not EOF(intFileNum1)

  Input(intFileNum1, strYear)

  Input(intFileNum1, strStatWeek)

  Input(intFileNum1, intChinHand)

  Input(intFileNum1, dblCohoHand)

  Input(intFileNum1, strLandHand)

  Input(intFileNum1, dblChin)

  Input(intFileNum1, intCoho)

  Input(intFileNum1, strLand)

  Input(intFileNum1, strDistrict)

  If StatWeek = strStatWeek And District = strDistrict Then

  Table6.Rows.Add(New Object() {Year2, District, StatWeek, 0, 0, ChinHand, CohoHand, LandHand, intChinHand, dblCohoHand, Chin, Coho, Land, strLandHand, dblChin})

  Else

  Year2 = strYear

  ChinHand = intChinHand

  CohoHand = dblCohoHand

  LandHand = strLandHand

  Chin = dblChin

  Coho = intCoho

  Land = strLand

  District = strDistrict

  StatWeek = strStatWeek

  End If

  Loop

  FileClose(intFileNum1)

  Dim MergeDataView2 As New DataView(EffortDataset.Tables("Table6"))

  s = ""

  For z = 0 To MergeDataView2.Table.Rows.Count - 1

  For y = 0 To MergeDataView2.Table.Columns.Count - 1

  If y = MergeDataView2.Table.Columns.Count - 1 Then

  s = s & MergeDataView2(z).Item(y).ToString

  Else

  s = s & MergeDataView2(z).Item(y).ToString & ","

  End If

  Next

  s = s & vbCrLf

  Next

  FileClose(intFileNum1)

  Dim sw As StreamWriter = New StreamWriter("K:\FinalEffort.xls", False)

  sw.WriteLine(s)

  sw.Close()

  thanks

  
  

• squeefy

  Excel will handle a comma delimited file just as easy as a tab delimited file
  
  

• brianpaulflynn

  I am having a very simple idea..
  1)Just replace the commas with tab <vbtab> store it in a string
  2)create a StreamWriter with filename ***.xls
  3)just write the content in to the file..
  note: tab seperated values will automatically occupy the cells and vbcrlf will get you the next row.,
  this works in MS Office XP suite
  
  str is your content
  Dim sw As StreamWriter = New StreamWriter("xyz.xls", false)
  content = str.replace(",",vbtab)
  sw.WriteLine(content)
  sw.Close()
  
  
  
  
  

• FHolcomb

  OK
  An interesting article which will show you how to get VB.Net to interact with Excel.

  How to automate Excel from VB.Net
  http://support.microsoft.com/kb/q301982/

  
  Here's an example which will add the contents of an array into an excel spreadsheet. You'll need to ensure that you set the reference to the Excel Object Class Library. Now reading you File form

  You would be wise to look at the TextFieldParser Object

  Parsing Text Files with the TextFieldParser Object
  http://msdn2.microsoft.com/en-US/library/x710fk43.aspx

  
  All you need to do is read from the text field and then can use the Excel object model to actually insert contents into a excel file (xls).

  
  

• John12312

  The easy way is to use excel to open the file as a txt or csv and then let the wizard walk you through...Otherwise in vb you are talking about opening either an ADO connection or TextFileParser on the original text file and then using Interop or ADO again to write to excel
  
  
• export data from file to excel