how to use Excel.Workbooks

  • Thread starter Thread starter touf
  • Start date Start date
T

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
 
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.ReleaseComObject(oExcel)

oExcel = Nothing
 
touf said:
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: 
[/QUOTE]

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.ReleaseComObject(oExcel)
oExcel = Nothing


The code can be compiled now. I didn't test it, but do you still have some
problems?
Click to expand...
 
Sorry,
my previous post wasn't for this problem...This problem was fixed.
Thanks.

Armin Zingler said:
touf said:
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: 
[/QUOTE]

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.ReleaseComObject(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
[/QUOTE]
Click to expand...
Click to expand...
 
* "touf said:
my previous post wasn't for this problem...This problem was fixed.
Click to expand...

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