ExcelBanter - vb.net interop

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - vb.net interop (https://www.excelbanter.com/excel-programming/378019-vbulletin-net-interop.html)

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

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