how can i pass data to Excel

Wed, 14 Dec 2005 17:18:52 Z

Please can anyone can help? The following code works properly in Visual Basic 2003 but not in Visual Basic 2005 Express.

        Dim EXL As New Excel.Application()
        If EXL Is Nothing Then
            MsgBox("Couldn't start Excel")
            Exit Sub
        End If

Dim WSheet As New Excel.Worksheet

EXL.Application.Visible = True

WSheet = EXL.Workbooks.Add.Worksheets.Add

At the last line I get an runtime error : "Old format or invalid type library."

how can i pass data to Excel

Thu, 15 Dec 2005 08:29:39 Z

trianta99,

I found another source of information about writing to Excel on the net and modified it to my specifications. I left out the DIM statements, but the code does work.

CindyKS

        ' Create the Excel application.

        excel_app = CreateObject("Excel.Application")
        excel_app.Visible = True

        ' Create a new spreadsheet.

        excel_app.Workbooks.Add()

        ' Insert Header data into Excel.

        With excel_app
            .Range("A1").Select()
            .ActiveCell.FormulaR1C1 = "Automated Client Billing"
            .Columns("A:A").ColumnWidth = 10
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Italic = True
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Range("A2").Select()
            .ActiveCell.FormulaR1C1 = "Cost Ctr"
            .Columns("A:A").ColumnWidth = 10
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Italic = True
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("D:D").ColumnWidth = 40
            .Range("D3").Select()
            .ActiveCell.FormulaR1C1 = "Client Billing"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 14
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Range("A6").Select()
            .ActiveCell.FormulaR1C1 = "Customer Code"
            .Columns("A:A").ColumnWidth = 10
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Italic = True
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Range("C6").Select()
            .ActiveCell.FormulaR1C1 = "ID"
            .Columns("C:C").ColumnWidth = 10
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Italic = True
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("E:E").ColumnWidth = 10
            .Range("e6").Select()
            .ActiveCell.FormulaR1C1 = "Documents"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("F:F").ColumnWidth = 10
            .Range("F6").Select()
            .ActiveCell.FormulaR1C1 = "Pages"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("G:G").ColumnWidth = 10
            .Range("G6").Select()
            .ActiveCell.FormulaR1C1 = "Calc"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("I:I").ColumnWidth = 10
            .Range("I6").Select()
            .ActiveCell.FormulaR1C1 = "Product"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With

            .Columns("K:K").ColumnWidth = 10
            .Range("K6").Select()
            .ActiveCell.FormulaR1C1 = "Client Totals"
            With .Selection.Font
                .Name = "Arial"
                .FontStyle = "Bold"
                .Size = 10
                .Strikethrough = False
                .Superscript = False
                .Subscript = False
                .OutlineFont = False
                .Shadow = False
                .Italic = False
                .Underline = False
                .ColorIndex = 1
            End With
        End With

    'Write the Billing Row Data

        row = row + 1
        StrVTotalItem = Format(VTotalItem, "0.00")
        With excel_app

            .Range("C" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = VClient
            .Range("D" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = VID
            .Range("E" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = VDocuments
            .Range("F" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = VPages
            .Range("G" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = VCostCalc
            .Range("H" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = StrVTotalItem
            .Range("I" & Format$(row)).Select()
            .ActiveCell.FormulaR1C1 = "21062"
        End With

        ' Comment the rest of the lines to keep
        ' Excel running so you can see it.

        ' Close the workbook without saving.
        'excel_app.ActiveWorkbook.Close(False)

        ' Close Excel.
        'excel_app.Quit()
        'excel_app = Nothing

        'end the Excel file

how can i pass data to Excel

Thu, 15 Dec 2005 14:42:32 Z

hi there,

i tried both methods of creating the new spreadsheet, but with both i get the error:

Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))

if anyone could help me with this i'd really appreciate it

thx in advance

how can i pass data to Excel

Thu, 15 Dec 2005 14:47:42 Z

i take again the same error at the same line

excel_app.Workbooks.Add()

"Old format or invalid type library. (Exception from HRESULT: 0x80028018 (TYPE_E_INVDATAREAD))"

how can i pass data to Excel

Thu, 15 Dec 2005 16:08:39 Z

i found the cause of the problem, it's because the user local settings are different from the office version, i use the english version and after setting language to english us, and country to united states in the reagional and language settings under control panel it worked fine

an other option is to use this code:

Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True
Dim oBooks As Object = oApp.Workbooks
Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US")
oBooks.GetType().InvokeMember("Add", Reflection.BindingFlags.InvokeMethod, Nothing, oBooks, Nothing, ci)

or this one:

Dim oApp As New Excel.Application()
oApp.Visible = True
oApp.UserControl = True
Dim oldCI As System.Globalization.CultureInfo = _
System.Threading.Thread.CurrentThread.CurrentCulture
System.Threading.Thread.CurrentThread.CurrentCulture = _
New System.Globalization.CultureInfo("en-US")
oApp.Workbooks.Add()
System.Threading.Thread.CurrentThread.CurrentCulture = oldCI

peace

how can i pass data to Excel

Fri, 16 Dec 2005 12:46:16 Z

You are perfect!

Can anyone from Microsoft tell us why does it happen?

Thanks very much

how can i pass data to Excel

Thu, 12 Jan 2006 12:34:19 Z

Microsoft Visual Studio 2005

.Net Framwork2

Office2003 sp1 full and update office2003-KB907417-FullFile-ENU

when i try to open Excel File i recrieve error

Old format or inval type library.(Exception from HRESULT:0x80020818(TYPE_E_INVDATAREAD))

i try to do follwing which you do but it not work

what should i do ?

how can i pass data to Excel

Thu, 12 Jan 2006 14:02:12 Z

I tried the solution of "gunner2002be" and now i can open excel files.

Try to search within the forum because you need a full instalation of Office 2003.

how can i pass data to Excel

Fri, 13 Jan 2006 06:32:58 Z

my code

(1) : CultureInfo oldCl = System.Threading.Thread.CurrentThread.CurrentCulture;

(2) :System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US");

(3) : System.Threading.Thread.CurrentThread.CurrentCulture = oldCl;

when i open excel file from vs2005 then error because

line (3) i set Culture = Old Culture it not en-US and when i comment this line i can open excel file and it work fine

thanks every body

how can i pass data to Excel

Fri, 13 Jan 2006 12:53:57 Z

trianta99 wrote:

Can anyone from Microsoft tell us why does it happen?

The issue is documented in the following Microsoft KB article:

http://support.microsoft.com/default.aspx?scid=kb;en-us;320369

how can i pass data to Excel

Thu, 17 Aug 2006 17:53:29 Z

Try this...

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

how can i pass data to Excel

Wed, 26 Mar 2008 18:07:26 Z

Hi everyone.

I did this and works locally(excel 07 installed), on my PC, but when I publish the website in a server (with excel 07 installed), the excel part won't run: no errors, just won't run

This is my code:

Dim oexcel As New Excel.Application()

oexcel.Visible = True

oexcel.UserControl = True

Dim oBooks As Excel.Workbook = oexcel.Workbooks.Add

Dim ci As System.Globalization.CultureInfo = New System.Globalization.CultureInfo("en-US")

Dim i, j As Int16

oexcel.Visible = True

oexcel.UserControl = True

Dim oSheet As Excel.Worksheet

oSheet = oBooks.Worksheets("sheet1")

Try

oSheet.Range("A1:F1").ColumnWidth = 15

oSheet.Cells(1, 2).Value = "Dep Week"

oSheet.Cells(1, 3).Value = "Date"

oSheet.Cells(1, 4).Value = "% of time"

oSheet.Cells(1, 5).Value = "Sched Users"

oSheet.Cells(1, 6).Value = "Users left"

Any suggestions?

how can i pass data to Excel

Tue, 21 Oct 2008 13:40:48 Z

gunner2002be wrote:

an other option is to use this code:

or this one:

peace

WOW this one was a pain in the neck.

Thank you.