[Ailaz]

_HAWK_, on 29 January 2012 - 04:45 AM, said:

Now you can leave Option Strict On

Imports System.Runtime.InteropServices

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    'oExcel = CreateObject("Excel.Application") 'also late binding
    oExcel = New Excel.Application ' <- use this instead
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")
   
    Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
      oBook.Sheets.Add() ' add a new worksheet
      'no late binding here
      CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1), Excel.Worksheet).Name = csvFile
    'The line below will save only the active workbook with .xls extention that Exel Viewer can open
    oExcel.DisplayAlerts = False 'ovoid vb6 constants
    oExcel.AlertBeforeOverwriting = False
    oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\TestUBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
    oBook.Close()
    oExcel.Quit()
    'these methods will properly dispose the process for excel
    'check the task manager and sometimes you will see it open
    Marshal.FinalReleaseComObject(oExcel)
    Marshal.FinalReleaseComObject(oBook)
    oExcel = Nothing
    oBook = Nothing
    'no need to call GC.Collect now
  End Sub

Thank you Hawk
I modified it so I could use open an existing excel file to add in the copied csv data.

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim oExcel As Excel.Application
        Dim oBook As Excel.Workbook
        'oExcel = CreateObject("Excel.Application") 'also late binding
        oExcel = New Excel.Application ' <- use this instead
        oExcel.Visible = True
        oBook = oExcel.Workbooks.Add("J:\vb\RizRandom\RizRandom\Results\UBM.xlsx")
        Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")

        Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
        oBook.Sheets.Add() ' add a new worksheet
        'no late binding here
        CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
        CType(oBook.Sheets(1), Excel.Worksheet).Paste()
        CType(oBook.Sheets(1), Excel.Worksheet).Name = csvFile

        'oBook.Sheets(1).name = csvFile
        'The line below will save only the active workbook with .xls extention that Exel Viewer can open
        oExcel.DisplayAlerts = False 'ovoid vb6 constants
        oExcel.AlertBeforeOverwriting = False
        oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\UBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
        oBook.Close()
        oExcel.Quit()
        'these methods will properly dispose the process for excel
        'check the task manager and sometimes you will see it open
        Marshal.FinalReleaseComObject(oExcel)
        Marshal.FinalReleaseComObject(oBook)
        oExcel = Nothing
        oBook = Nothing
        

However the rest of my apllication also generates csv files. Is it sensible to add each csv file as above to a different worksheet? Once all of these have been combined into the excel file the user has to email it back to me.

[Ailaz]

_HAWK_, on 29 January 2012 - 04:45 AM, said:

Now you can leave Option Strict On

Imports System.Runtime.InteropServices

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    'oExcel = CreateObject("Excel.Application") 'also late binding
    oExcel = New Excel.Application ' <- use this instead
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")
   
    Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
      oBook.Sheets.Add() ' add a new worksheet
      'no late binding here
      CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1), Excel.Worksheet).Name = csvFile
    'The line below will save only the active workbook with .xls extention that Exel Viewer can open
    oExcel.DisplayAlerts = False 'ovoid vb6 constants
    oExcel.AlertBeforeOverwriting = False
    oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\TestUBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
    oBook.Close()
    oExcel.Quit()
    'these methods will properly dispose the process for excel
    'check the task manager and sometimes you will see it open
    Marshal.FinalReleaseComObject(oExcel)
    Marshal.FinalReleaseComObject(oBook)
    oExcel = Nothing
    oBook = Nothing
    'no need to call GC.Collect now
  End Sub

How do I combine several csv files in one swoop?

[_HAWK_]

I would probably use a sub that passes the file name as a parameter and the sheet it adds it to. The sub would open the StreamReader and add the data much like you have above.

This post has been edited by _HAWK_: 31 January 2012 - 10:36 PM

[Ailaz]

_HAWK_, on 01 February 2012 - 05:36 AM, said:

I would probably use a sub that passes the file name as a parameter and the sheet it adds it to. The sub would open the StreamReader and add the data much like you have above.

Thanks Hawk

[Beach_Coder]

_HAWK_, on 28 January 2012 - 11:45 PM, said:

Now you can leave Option Strict On

Imports System.Runtime.InteropServices

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    'oExcel = CreateObject("Excel.Application") 'also late binding
    oExcel = New Excel.Application ' <- use this instead
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")
   
    Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
      oBook.Sheets.Add() ' add a new worksheet
      'no late binding here
      CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1), Excel.Worksheet).Name = csvFile
    'The line below will save only the active workbook with .xls extention that Exel Viewer can open
    oExcel.DisplayAlerts = False 'ovoid vb6 constants
    oExcel.AlertBeforeOverwriting = False
    oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\TestUBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
    oBook.Close()
    oExcel.Quit()
    'these methods will properly dispose the process for excel
    'check the task manager and sometimes you will see it open
    Marshal.FinalReleaseComObject(oExcel)
    Marshal.FinalReleaseComObject(oBook)
    oExcel = Nothing
    oBook = Nothing
    'no need to call GC.Collect now
  End Sub

Very nice work Hawk. Splendid.

[Ailaz]

_HAWK_, on 29 January 2012 - 04:45 AM, said:

Now you can leave Option Strict On

Imports System.Runtime.InteropServices

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    Dim oExcel As Excel.Application
    Dim oBook As Excel.Workbook
    'oExcel = CreateObject("Excel.Application") 'also late binding
    oExcel = New Excel.Application ' <- use this instead
    oExcel.Visible = True
    oBook = oExcel.Workbooks.Add
    Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")
   
    Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
      oBook.Sheets.Add() ' add a new worksheet
      'no late binding here
      CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1), Excel.Worksheet).Name = csvFile
    'The line below will save only the active workbook with .xls extention that Exel Viewer can open
    oExcel.DisplayAlerts = False 'ovoid vb6 constants
    oExcel.AlertBeforeOverwriting = False
    oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\TestUBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
    oBook.Close()
    oExcel.Quit()
    'these methods will properly dispose the process for excel
    'check the task manager and sometimes you will see it open
    Marshal.FinalReleaseComObject(oExcel)
    Marshal.FinalReleaseComObject(oBook)
    oExcel = Nothing
    oBook = Nothing
    'no need to call GC.Collect now
  End Sub

Still not managing to add each csv into different sheets...still pastes over the same thing over and over..

[DimitriV]

Well yeah, it only ever copies the first sheet!

CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1)

[Beach_Coder]

You've got to add a new sheet, and then make sure you paste to the new sheet.

It would substantially quicker if you used Move. If you have a csv file open in Excel, use Excel's Move command and move that sheet to the workbook of your choice. No copying, no pasting, no need to first add a new sheet.

[Ailaz]

jimmyBo, on 11 February 2012 - 11:42 PM, said:

Well yeah, it only ever copies the first sheet!

CType(oBook2.Sheets(1), Excel.Worksheet).Cells.Copy()
      CType(oBook.Sheets(1), Excel.Worksheet).Paste()
      CType(oBook2.Sheets(1)

Yeap realised that so was using the Add After:= aswell ...

[Ailaz]

Beach_Coder, on 11 February 2012 - 11:46 PM, said:

You've got to add a new sheet, and then make sure you paste to the new sheet.

It would substantially quicker if you used Move. If you have a csv file open in Excel, use Excel's Move command and move that sheet to the workbook of your choice. No copying, no pasting, no need to first add a new sheet.

Do the csvfiles & excel files all need to be open?

[Beach_Coder]

Ailaz, on 11 February 2012 - 06:57 PM, said:

Beach_Coder, on 11 February 2012 - 11:46 PM, said:

You've got to add a new sheet, and then make sure you paste to the new sheet.

It would substantially quicker if you used Move. If you have a csv file open in Excel, use Excel's Move command and move that sheet to the workbook of your choice. No copying, no pasting, no need to first add a new sheet.

Do the csvfiles & excel files all need to be open?

I've not tried it otherwise. My suspicion is that for either Move or Copy to work they do need to be open. Now that I think about it, when I have failed to open something that I want to access it generates an error unless the code specifies that if the workbook xyz isn't open that it should open it and then do whatever needs doing. If instead you connect to file xyz as a data source then it would not need to be open but you would then be importing data rather than copying it.

[Ailaz]

Hmmmm...

Hmmmm...

[Beach_Coder]

Ailaz, on 11 February 2012 - 07:08 PM, said:

Hmmmm...

Hmmmm...

Indeed.

[Ailaz]