By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,996 Members | 1,051 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,996 IT Pros & Developers. It's quick & easy.

how to use Excel.Workbooks

P: n/a
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

Nov 20 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
* "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>
Nov 20 '05 #2

P: n/a
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>

Nov 20 '05 #3

P: n/a
"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

Nov 20 '05 #4

P: n/a
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

Nov 20 '05 #5

P: n/a
* "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>
Nov 20 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.