Excel Automation

[apgoodb]

I have a database that I built some functionality to export a query to
excel and then do some formatting. After reading a lot of comments it
seems I need to use "late binding", because some of the users use 2000
while the others use 2002. This worked using early binding, but after
changing the code to what I thought I needed I have a few errors.
Here is the code:
Dim xlbook as object
Set xlBook = GetObject([txtdirectory], "Excel.Applicat ion")
xlBook.Applicat ion.Visible = True
xlBook.Windows( 1).Visible = True
xlBook.Workshee ts("Benefit_Exp ort_Query").Vis ible = True
xlBook.Workshee ts("Benefit Export Query").rows("1 :3").Insert
Shift:=xlDown
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").ColumnW idth = 25
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Value =
[Text41]
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Font.Siz e =
12
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Font.Bol d =
True
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").Horizon talAlignment = xlLeft
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").Vertica lAlignment = xlTop
xlBook.Workshee ts("Benefit Export Query").rows("2 :9999").WrapTex t
= True
xlBook.Workshee ts("Benefit Export Query").rows("1 :9999").AutoFit

The first errors I get are Compile error variable not defined with the
xldown, xlleft, and xltop. However, even if I take these out, nothing
is happening with my workbook. It exports, but no formatting. Any
ideas would be appreciated.
Thanks,

[Fletcher Arnold]

"apgoodb" <ap*****@yahoo. com> wrote in message
news:86******** *************** **@posting.goog le.com...

I have a database that I built some functionality to export a query to
excel and then do some formatting. After reading a lot of comments it
seems I need to use "late binding", because some of the users use 2000
while the others use 2002. This worked using early binding, but after
changing the code to what I thought I needed I have a few errors.
Here is the code:
Dim xlbook as object
Set xlBook = GetObject([txtdirectory], "Excel.Applicat ion")
xlBook.Applicat ion.Visible = True
xlBook.Windows( 1).Visible = True
xlBook.Workshee ts("Benefit_Exp ort_Query").Vis ible = True
xlBook.Workshee ts("Benefit Export Query").rows("1 :3").Insert
Shift:=xlDown
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").ColumnW idth = 25
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Value =
[Text41]
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Font.Siz e =
12
xlBook.Workshee ts("Benefit Export Query").Range(" a1").Font.Bol d =
True
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").Horizon talAlignment = xlLeft
xlBook.Workshee ts("Benefit Export
Query").rows("1 :9999").Vertica lAlignment = xlTop
xlBook.Workshee ts("Benefit Export Query").rows("2 :9999").WrapTex t
= True
xlBook.Workshee ts("Benefit Export Query").rows("1 :9999").AutoFit

The first errors I get are Compile error variable not defined with the
xldown, xlleft, and xltop. However, even if I take these out, nothing
is happening with my workbook. It exports, but no formatting. Any
ideas would be appreciated.
Thanks,

Although you use late-binding when you hand over the database, you should
always 'cheat' and use early binding first. This will let you use the
constants such as xldown and give the benefit of intellisense to help you
with the Excel object model. When you finally hand it over, you will need t
o remove the reference to Excel, which means that the machine will no longer
recognise these constants.
So before you remove the reference, you could type CTRL-G to get the
immediate window and type in:
Msgbox "xldown = " & xldown
Which shows you that xldown has the value -4121. You could then adjust your
code and put in these constants, or use your own to keep them separate, eg
Const XL_DOWN = -4121.

Fletcher