Convert CSV to XLS

[mrnervios]

I have a form that deals with excel (csv, xls) to convert a CSV file to a XLS file and adjust a few columns.

I use the resulting file to upload the data to a msaccess table. The csv file is downloaded from the internet and is the only format available.

The problem I have is that there a couple of fields in the CSV that because the lenght is more than 12 digits converts it to an exponential format. I was able to convert it back to a straight number in VS2008 from a conversion from VB6, but I am trying to make it in a VS2010.

I will appreciate any help I can get because it does not convert to a number but a bunch of symbols.

My code is as follows,

Imports System.Data
Imports Excel = Microsoft.Office.Interop.Excel

Public Class Form8

Public Sub CsvPaid()
' On Error GoTo errorHandler
Dim Pathfile As String
Dim strCSVPath As String
' Dim xRows As Short
' Dim xCols As Short
' Dim fileTitle As String
Dim objExcel As Excel.Application
Dim objXsheet As Excel.Worksheet
' Dim r As Short
Dim rg As Excel.Range '= oSheet.Columns("A:A") ' delete the specific column
objExcel = New Excel.Application
Try
strCSVPath = TextBox1.Text
' strCSVPath = "G:\FileExchange_Response_10965185_071911.csv"
objExcel.Workbooks.Open(Filename:=strCSVPath)

objXsheet = objExcel.Worksheets(1)

Pathfile = Me.TextBox2.Text
' Pathfile = "G:\FileExchange_Response_10965185_071911"

' objExcel.ActiveWorkbook.SaveAs(Filename:=Pathfile, FileFormat:=objExcel.Windows.XlWindowState.xlNormal)
objExcel.ActiveWorkbook.SaveAs(Filename:=Pathfile, FileFormat:=Excel.XlWindowState.xlNormal)
' r = objExcel.WorksheetFunction.CountA(Range("T:T")) + 1

' MsgBox "count = " & r

' objExcel.Range("L:N").Select()
objExcel.Columns.Select.Equals("L:N")
' objExcel.Selection.numberformat = "0"
objExcel.Selection.numberformat = "0"

objExcel.Columns.Select.Equals("X:AA")
objExcel.Selection.NumberFormat = "mm/dd/yy"

' objExcel.Columns.Select.Equals("AJ:AN")
rg = objExcel.Columns("AJ:AN")
rg.Delete()

'UPGRADE_WARNING: Couldn't resolve default property of object objExcel.Selection.Clear. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'

' objExcel.Columns.Clear()

'UPGRADE_WARNING: Couldn't resolve default property of object objExcel.Columns().Select. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'

' objExcel.Columns.Select.Equals("T:T")
' Do

'If IsNothing(ExcelGlobal_definst.ActiveCell._Default) Then
'ExcelGlobal_definst.ActiveCell.FormulaR1C1 = "C$ 0.00"
'End If
'ExcelGlobal_definst.ActiveCell.Offset(1, 0).Select()

'Loop Until IsNothing(ExcelGlobal_definst.ActiveCell.Offset(0, 1)._Default)

'UPGRADE_WARNING: Couldn't resolve default property of object objExcel.Sheets().Name. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6A50421D-15FE-4896-8A1B-2EC21E9037B2"'
Catch ex As Exception
MsgBox(ex.ToString)
End Try
objExcel.Sheets(1).Name = "Sheet1"

objExcel.ActiveWorkbook.Close(SaveChanges:=True)


'UPGRADE_NOTE: Object objXsheet may not be destroyed until it is garbage collected. Click for more: 'ms-help://MS.VSCC.v90/dv_commoner/local/redirect.htm?keyword="6E35BFF6-CD74-4B09-9689-3E1A43DF8969"'

objXsheet = Nothing

objExcel.Quit()

objExcel = Nothing

GC.Collect()
GC.WaitForFullGCComplete()


' Me.FileName.Text = ""
' Me.Text1.Text = ""
errorHandler:
Exit Sub
End Sub