how to use Excel.Workbooks

[touf]

Hi,
I've an error when I declare
Dim oBook As Excel.Workbooks

What imports, and reference do I need to use Excel.Workbooks class?

Thanks

[Herfried K. Wagner [MVP]]

* "touf" <to******@hotmail.com> scripsit:

I've an error when I declare
Dim oBook As Excel.Workbooks

What imports, and reference do I need to use Excel.Workbooks class?

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<http://support.microsoft.com/default.aspx?scid=kb;en-us;301982>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

Improve your quoting style:
<http://learn.to/quote>
<http://www.plig.net/nnq/nquote.html>

[touf]

Thanks Herfried, but it doesn't resolve the problem
here is the whole code, can you please take a look to see what is wrong
Thanks.

Dim oExcel As Object

Dim oBook As Excel.Workbook

Dim oSheet As Object

oExcel = CreateObject("Excel.Application")

oBook = oExcel.Workbooks.Add

oSheet = oBook.Worksheets(1)

'Create the QueryTable object.

Dim oQryTable As Object

oQryTable = oSheet.QueryTables.Add(conString, oSheet.Range("A1"), sqlstring)

oQryTable.RefreshStyle = 2 ' xlInsertEntireRows = 2

oQryTable.Refresh(False)

'Save the workbook and quit Excel.

If Dir(fileName) <> "" Then Kill(fileName)

oBook.SaveAs(fileName)

oQryTable = Nothing

oSheet = Nothing

oBook.Close()

oBook = Nothing

oExcel.Quit()

System.Runtime.InteropServices.Marshal.ReleaseComO bject(oExcel)

oExcel = Nothing
"Herfried K. Wagner [MVP]" <hi***************@gmx.at> wrote in message
news:bn*************@ID-208219.news.uni-berlin.de...

* "touf" <to******@hotmail.com> scripsit:

I've an error when I declare
Dim oBook As Excel.Workbooks

What imports, and reference do I need to use Excel.Workbooks class?

HOWTO: Automate Microsoft Excel from Visual Basic .NET
<http://support.microsoft.com/default.aspx?scid=kb;en-us;301982>

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>

Improve your quoting style:
<http://learn.to/quote>
<http://www.plig.net/nnq/nquote.html>

[Armin Zingler]

"touf" <to******@hotmail.com> schrieb

Thanks Herfried, but it doesn't resolve the problem
here is the whole code, can you please take a look to see what is
wrong Thanks.

[code]

After setting a reference to the Excel Object library and declaring 3 string
variables, the code can't be compiled when Option Strict is used. You may
try to use Option Strict because it forces you to do explicit type casting.
This might reveal some errors.

....later...
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oQryTable As Excel.QueryTable

oExcel = DirectCast( _
CreateObject("Excel.Application"), Excel.Application _
)

oBook = oExcel.Workbooks.Add
oSheet = DirectCast(oBook.Worksheets(1), Excel.Worksheet)

'Create the QueryTable object.

oQryTable = oSheet.QueryTables.Add( _
constring, oSheet.Range("A1"), sqlstring _
)

oQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows
oQryTable.Refresh(False)

'Save the workbook and quit Excel.

If Dir(fileName) <> "" Then Kill(fileName)

oBook.SaveAs(filename)
oQryTable = Nothing
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(oExcel)
oExcel = Nothing
The code can be compiled now. I didn't test it, but do you still have some
problems?
--
Armin

http://learn.to/quote
http://www.plig.net/nnq/nquote.html

[touf]

Sorry,
my previous post wasn't for this problem...This problem was fixed.
Thanks.

"Armin Zingler" <az*******@freenet.de> wrote in message
news:uA**************@TK2MSFTNGP09.phx.gbl...

"touf" <to******@hotmail.com> schrieb

Thanks Herfried, but it doesn't resolve the problem
here is the whole code, can you please take a look to see what is
wrong Thanks.

[code]
After setting a reference to the Excel Object library and declaring 3

string variables, the code can't be compiled when Option Strict is used. You may
try to use Option Strict because it forces you to do explicit type casting. This might reveal some errors.

...later...
Dim oExcel As Excel.Application
Dim oBook As Excel.Workbook
Dim oSheet As Excel.Worksheet
Dim oQryTable As Excel.QueryTable

oExcel = DirectCast( _
CreateObject("Excel.Application"), Excel.Application _
)

oBook = oExcel.Workbooks.Add
oSheet = DirectCast(oBook.Worksheets(1), Excel.Worksheet)

'Create the QueryTable object.

oQryTable = oSheet.QueryTables.Add( _
constring, oSheet.Range("A1"), sqlstring _
)

oQryTable.RefreshStyle = Excel.XlCellInsertionMode.xlInsertEntireRows
oQryTable.Refresh(False)

'Save the workbook and quit Excel.

If Dir(fileName) <> "" Then Kill(fileName)

oBook.SaveAs(filename)
oQryTable = Nothing
oSheet = Nothing
oBook.Close()
oBook = Nothing
oExcel.Quit()
System.Runtime.InteropServices.Marshal.ReleaseComO bject(oExcel)
oExcel = Nothing
The code can be compiled now. I didn't test it, but do you still have some
problems?
--
Armin

http://learn.to/quote
http://www.plig.net/nnq/nquote.html

[Herfried K. Wagner [MVP]]

* "touf" <to******@hotmail.com> scripsit:

my previous post wasn't for this problem...This problem was fixed.

Glad to hear that. Nevertheless I would recommend to read Armin's
suggestions.

--
Herfried K. Wagner
MVP · VB Classic, VB.NET
<http://www.mvps.org/dotnet>