[Ailaz]

I'm working with vb.net 2008. My application generates 1 excel file (with login details etc). The application will store user results for various sections into csv files. I'm trying to combine the csv files into the excel file. I'm trying to get this to work with one csv file initially ...but I am ending up with a blank Excel file.
This is what I've tried so far:

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
        Dim oExcel As Object
        Dim oBook As Object
        Dim oSheet As Object

        oExcel = CreateObject("Excel.Application")
        oBook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\UBM.xlsx") ' Add
        oExcel.Visible = True
        oSheet = oBook.Worksheets(1)
  

        Dim csvFile As String = Dir("J:\*.csv")
        Do While csvFile <> ""
            oBook.Open("J:\" & csvFile)
            oBook.Sheets.Add() ' add a new worksheet
            oBook.Sheets(1).cells.copy()
            oBook.Sheets(1).paste()
            oBook.Sheets(1).name = csvFile 
            csvFile = Dir()
        Loop
    
        oExcel.DisplayAlerts = vbFalse
        oExcel.AlertBeforeOverwriting = vbFalse
        oBook.SaveAs("J:\vb\RizRandom\RizRandom\TestUBM.xlsx")
        oBook.Close()
     
        oExcel = Nothing
        GC.Collect()
     
    End Sub

Much appreciated................I'm so near finishing this.....

This post has been edited by Ailaz: 26 January 2012 - 04:24 PM

[Beach_Coder]

I can't clearly follow what oBook is (oBook = ?).
Make sure you are using Excel to open the csv file.

[Beach_Coder]

You open the csv file. Then you add a new worksheet. Then you are copying. All you are doing is copying a blank sheet. Make sure you copy the the csv file and not the new blank worksheet.

[Ailaz]

Beach_Coder, on 26 January 2012 - 11:38 PM, said:

I can't clearly follow what oBook is (oBook = ?).
Make sure you are using Excel to open the csv file.

oBook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\UBM.xlsx")

[Beach_Coder]

Ailaz, on 26 January 2012 - 06:49 PM, said:

Beach_Coder, on 26 January 2012 - 11:38 PM, said:

I can't clearly follow what oBook is (oBook = ?).
Make sure you are using Excel to open the csv file.

oBook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\UBM.xlsx")

Yes I saw that afterward thank you.

Also, you are pasting into Sheets(1) every time. When you add a new sheet, add it as:

Sheets.Add After:=Sheets(Sheets.Count)

Then Copy from the csv file, then paste into Sheets(Sheets.Count).

[Ailaz]

Beach_Coder, on 26 January 2012 - 11:53 PM, said:

Ailaz, on 26 January 2012 - 06:49 PM, said:

Beach_Coder, on 26 January 2012 - 11:38 PM, said:

I can't clearly follow what oBook is (oBook = ?).
Make sure you are using Excel to open the csv file.

oBook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\UBM.xlsx")

Yes I saw that afterward thank you.

Also, you are pasting into Sheets(1) every time. When you add a new sheet, add it as:

Sheets.Add After:=Sheets(Sheets.Count)

Then Copy from the csv file, then paste into Sheets(Sheets.Count).

Hmm "Sheets" not liked..

[Ailaz]

Going round and round in circles here..

[m_wylie85]

hey check this out it is about excel automation. I done something like what you are doing about 6 months ago it was nightmare to get working.

http://support.microsoft.com/kb/302094

I will also try to get my code from last time i done it to try to help you but it's 1am and i have work tomorrow so i will look for it when i get out of work tomorrow

good luck

[Ailaz]

m_wylie85, on 27 January 2012 - 01:01 AM, said:

hey check this out it is about excel automation. I done something like what you are doing about 6 months ago it was nightmare to get working.

http://support.microsoft.com/kb/302094

I will also try to get my code from last time i done it to try to help you but it's 1am and i have work tomorrow so i will look for it when i get out of work tomorrow

good luck

Thanks m_wylie85....still not getting any though

[m_wylie85]

Hey I had a look for that code must have deleted it. But Have a look at these posts of mine when i was having trouble with excel automation the code from these post are pretty much right hop it helps

http://www.dreaminco...1&#entry1436349

http://www.dreaminco...1&#entry1429628

[Ailaz]

m_wylie85, on 29 January 2012 - 12:49 AM, said:

Hey I had a look for that code must have deleted it. But Have a look at these posts of mine when i was having trouble with excel automation the code from these post are pretty much right hop it helps

http://www.dreaminco...1&#entry1436349

http://www.dreaminco...1&#entry1429628

Thanks m_wylie85 ....Geez I'm shattered. Why am I doing this to myself? I've come along with this application - I will not give up now !

[Ailaz]

Beach_Coder, on 26 January 2012 - 11:53 PM, said:

Ailaz, on 26 January 2012 - 06:49 PM, said:

Beach_Coder, on 26 January 2012 - 11:38 PM, said:

I can't clearly follow what oBook is (oBook = ?).
Make sure you are using Excel to open the csv file.

oBook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\UBM.xlsx")

Yes I saw that afterward thank you.

Also, you are pasting into Sheets(1) every time. When you add a new sheet, add it as:

Sheets.Add After:=Sheets(Sheets.Count)

Then Copy from the csv file, then paste into Sheets(Sheets.Count).

"After" not declared error

[Ailaz]

This is what works so far....

    
    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")
        oExcel.Visible = True
        oBook = oExcel.Workbooks.Add '("J:\vb\RizRandom\RizRandom\UBM.xlsx")
        Dim csvFile As String = Dir("J:\vb\RizRandom\RizRandom\Results\poo.csv")
        Do While csvFile <> ""
            Dim oBook2 As Excel.Workbook = oExcel.Workbooks.Open("J:\vb\RizRandom\RizRandom\Results\poo.csv")
            oBook.Sheets.Add() ' add a new worksheet
            oBook2.Sheets(1).cells.copy()
            'oBook.Sheets.Add(After):=Sheets(Sheets.Count))
            oBook.Sheets(1).paste()
            oBook.Sheets(1).name = csvFile
            csvFile = Dir()
        Loop

        'The line below will save only the active workbook with .xls extention that Exel Viewer can open
        oExcel.DisplayAlerts = vbFalse
        oExcel.AlertBeforeOverwriting = vbFalse
        oBook.SaveAs("J:vb\RizRandom\RizRandom\Results\TestUBM.xlsx") '-4143) 'J:\vb\RizRandom\RizRandom\
        oBook.Close()
        oBook = Nothing
        oExcel.Quit()
        oExcel = Nothing
        GC.Collect()
        GC.WaitForPendingFinalizers()
        MessageBox.Show("Yeahyyyyyyyy")

[Ailaz]

Thank you m_wylie85 and Beach-Coder for your help

[_HAWK_]

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

This post has been edited by _HAWK_: 28 January 2012 - 09:57 PM