Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb.net interop
Hi,
While trying to merge CSV file into an Excel workbook (on file per tab), the data gets truncated to 255 characters either by the cut or the paste functions. The original CSV file cells contains all the characters while the resulting XLS file cells are truncated. Any idea why ? Public Function InsertCSVFilesIntoExcelFile( _ ByVal strSourceFolderPath As String, _ ByVal colCSVFileNames As Common.CCollection, _ ByVal strNewExcelFilePath As String, _ Optional ByVal blnSaveAndCloseExcelFile As Boolean = True) As OperationResult Dim oSourceExcel As Excel.Application Dim oSourceBook As Excel.Workbook Dim oSourceSheet As Excel.Worksheet Dim uclsSheet1 As Excel.Worksheet Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim f As Excel.XlFileFormat = Excel.XlFileFormat.xlExcel9795 Dim i As Integer Dim varSavedCursor As Object Me.ReadProcesses() If colCSVFileNames.Count = 0 Then Return CGridImportExport.OperationResult.ResultSuccess Try If blnSaveAndCloseExcelFile Then If Common.FileFunctions.DoesFileExist(strNewExcelFile Path) Then Common.FileFunctions.DeleteFile(strNewExcelFilePat h) End If End If ' save the crrent screen mouse pointer varSavedCursor = System.Windows.Forms.Cursor.Current Catch Debug.Assert(False) Err.Clear() End Try Try oSourceExcel = New Excel.Application oExcel = New Excel.Application oBook = oExcel.Workbooks.Add() Try For i = oBook.Sheets.Count To 2 Step -1 'CType(oBook.Sheets.Item(1), Excel.Worksheet).Delete() oBook.Sheets.Item(i).Delete() Next uclsSheet1 = oBook.Sheets.Item(1) Catch Debug.Assert(False) Err.Clear() End Try For i = 1 To colCSVFileNames.Count If Common.FileFunctions.DoesFileExist(strSourceFolder Path & colCSVFileNames.Item(i)) Then oSourceBook = oSourceExcel.Workbooks.Open(strSourceFolderPath & colCSVFileNames.Item(i)) oSourceSheet = DirectCast(oSourceBook.Sheets.Item(1), Excel.Worksheet) oSourceSheet.Cells.Copy() oBook.Sheets.Add() oSheet = DirectCast(oBook.Sheets.Item(1), Excel.Worksheet) oSheet.Name = Common.StringFunctions.LeftSide(colCSVFileNames.It em(i), ".") oSheet.Paste() ' Copy a smaller amount of data into the clipboard to prevent keeping a large amount in memory oExcel.Range("A1").Select() oSourceExcel.Range("A1").Copy() oSourceExcel.DisplayAlerts = False oSourceBook.Close(False) oSourceExcel.DisplayAlerts = True Else Debug.Assert(False) End If 'CopyCSVFileToSheet(oSheet, strFolderPath & "\" & colCSVFileNames.Item(i)) Next ' Now delete sheet1 uclsSheet1.Delete() If blnSaveAndCloseExcelFile Then oSourceExcel.DisplayAlerts = False oBook.SaveAs(strNewExcelFilePath, f) oSourceExcel.DisplayAlerts = True End If Catch ex As Exception Debug.Assert(False) Common.ErrorReporting.LogFile.Add(Err.Description) ' restore the screen mouse pointer System.Windows.Forms.Cursor.Current = varSavedCursor Return OperationResult.ResultError End Try Try oSheet = Nothing If blnSaveAndCloseExcelFile Then oBook.Close(False) End If oBook = Nothing If blnSaveAndCloseExcelFile Then oExcel.DisplayAlerts = False oExcel.Workbooks.Close() oExcel.Quit() Else oExcel.Visible = True End If oExcel = Nothing oSourceExcel.DisplayAlerts = False oSourceExcel.Workbooks.Close() oSourceBook = Nothing oSourceSheet = Nothing oSourceExcel.Quit() oSourceExcel = Nothing ' restore the screen mouse pointer System.Windows.Forms.Cursor.Current = varSavedCursor Catch Debug.Assert(False) Err.Clear() End Try Me.CleanProcesses() Return OperationResult.ResultSuccess End Function |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
vb.net interop
I actually did some more testing and the problem is with the pasting into a
new workbook. I've put a breakpoint right after the cells.copy() and started pasting the clipboard content into various application and only Excel 2003 was truncating the cells. "Cell truncation in vb.net interop" wrote: Hi, While trying to merge CSV file into an Excel workbook (on file per tab), the data gets truncated to 255 characters either by the cut or the paste functions. The original CSV file cells contains all the characters while the resulting XLS file cells are truncated. Any idea why ? Public Function InsertCSVFilesIntoExcelFile( _ ByVal strSourceFolderPath As String, _ ByVal colCSVFileNames As Common.CCollection, _ ByVal strNewExcelFilePath As String, _ Optional ByVal blnSaveAndCloseExcelFile As Boolean = True) As OperationResult Dim oSourceExcel As Excel.Application Dim oSourceBook As Excel.Workbook Dim oSourceSheet As Excel.Worksheet Dim uclsSheet1 As Excel.Worksheet Dim oExcel As Excel.Application Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Dim f As Excel.XlFileFormat = Excel.XlFileFormat.xlExcel9795 Dim i As Integer Dim varSavedCursor As Object Me.ReadProcesses() If colCSVFileNames.Count = 0 Then Return CGridImportExport.OperationResult.ResultSuccess Try If blnSaveAndCloseExcelFile Then If Common.FileFunctions.DoesFileExist(strNewExcelFile Path) Then Common.FileFunctions.DeleteFile(strNewExcelFilePat h) End If End If ' save the crrent screen mouse pointer varSavedCursor = System.Windows.Forms.Cursor.Current Catch Debug.Assert(False) Err.Clear() End Try Try oSourceExcel = New Excel.Application oExcel = New Excel.Application oBook = oExcel.Workbooks.Add() Try For i = oBook.Sheets.Count To 2 Step -1 'CType(oBook.Sheets.Item(1), Excel.Worksheet).Delete() oBook.Sheets.Item(i).Delete() Next uclsSheet1 = oBook.Sheets.Item(1) Catch Debug.Assert(False) Err.Clear() End Try For i = 1 To colCSVFileNames.Count If Common.FileFunctions.DoesFileExist(strSourceFolder Path & colCSVFileNames.Item(i)) Then oSourceBook = oSourceExcel.Workbooks.Open(strSourceFolderPath & colCSVFileNames.Item(i)) oSourceSheet = DirectCast(oSourceBook.Sheets.Item(1), Excel.Worksheet) oSourceSheet.Cells.Copy() oBook.Sheets.Add() oSheet = DirectCast(oBook.Sheets.Item(1), Excel.Worksheet) oSheet.Name = Common.StringFunctions.LeftSide(colCSVFileNames.It em(i), ".") oSheet.Paste() ' Copy a smaller amount of data into the clipboard to prevent keeping a large amount in memory oExcel.Range("A1").Select() oSourceExcel.Range("A1").Copy() oSourceExcel.DisplayAlerts = False oSourceBook.Close(False) oSourceExcel.DisplayAlerts = True Else Debug.Assert(False) End If 'CopyCSVFileToSheet(oSheet, strFolderPath & "\" & colCSVFileNames.Item(i)) Next ' Now delete sheet1 uclsSheet1.Delete() If blnSaveAndCloseExcelFile Then oSourceExcel.DisplayAlerts = False oBook.SaveAs(strNewExcelFilePath, f) oSourceExcel.DisplayAlerts = True End If Catch ex As Exception Debug.Assert(False) Common.ErrorReporting.LogFile.Add(Err.Description) ' restore the screen mouse pointer System.Windows.Forms.Cursor.Current = varSavedCursor Return OperationResult.ResultError End Try Try oSheet = Nothing If blnSaveAndCloseExcelFile Then oBook.Close(False) End If oBook = Nothing If blnSaveAndCloseExcelFile Then oExcel.DisplayAlerts = False oExcel.Workbooks.Close() oExcel.Quit() Else oExcel.Visible = True End If oExcel = Nothing oSourceExcel.DisplayAlerts = False oSourceExcel.Workbooks.Close() oSourceBook = Nothing oSourceSheet = Nothing oSourceExcel.Quit() oSourceExcel = Nothing ' restore the screen mouse pointer System.Windows.Forms.Cursor.Current = varSavedCursor Catch Debug.Assert(False) Err.Clear() End Try Me.CleanProcesses() Return OperationResult.ResultSuccess End Function |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Increase columns with interop | Excel Programming | |||
After Save Event (Interop) | Excel Programming | |||
c# & InterOp & OptionButtons | Excel Programming | |||
Interop problem with VS.NET 2003 | Excel Programming | |||
Interop and VS Behavior | Excel Programming |