how can i pass data to Excel

Question

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."

Answer 1

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

Answer 2

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

Answer 3

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))"

Answer 4

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

Answer 5

You are perfect!

Can anyone from Microsoft tell us why does it happen?

Thanks very much

Answer 6

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 ?

Answer 7

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.

Answer 8

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

Answer 9

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

Answer 10

Try this...

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

Answer 11

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?

Answer 12

gunner2002be wrote:

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

 

WOW this one was a pain in the neck.

 

Thank you.

Answer 13

It works for me with the suggestion of gunner2002be

 

Thanks

Answer 14

THANKS GUNNER2002,

 

THIS WAS A LIFE SAVER FOR ME, AS THIS ERROR WAS MAKING ME GO CRAZY WITH VARIOUS FORUMS TALKING ME ROUNDABOUT ON THIS SMALLCONFIGURATION WHICH FIXED THE ISSUE.

 

GUYS THIS WORKS

 

 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

PRABIN KUMAR S