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.Application")
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Worksheets("Benefit_Export_Query").Visible = True
xlBook.Worksheets("Benefit Export Query").rows("1:3").Insert
Shift:=xlDown
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").ColumnWidth = 25
xlBook.Worksheets("Benefit Export Query").Range("a1").Value =
[Text41]
xlBook.Worksheets("Benefit Export Query").Range("a1").Font.Size =
12
xlBook.Worksheets("Benefit Export Query").Range("a1").Font.Bold =
True
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").HorizontalAlignment = xlLeft
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").VerticalAlignment = xlTop
xlBook.Worksheets("Benefit Export Query").rows("2:9999").WrapText
= True
xlBook.Worksheets("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.google.co m...

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.Application")
xlBook.Application.Visible = True
xlBook.Windows(1).Visible = True
xlBook.Worksheets("Benefit_Export_Query").Visible = True
xlBook.Worksheets("Benefit Export Query").rows("1:3").Insert
Shift:=xlDown
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").ColumnWidth = 25
xlBook.Worksheets("Benefit Export Query").Range("a1").Value =
[Text41]
xlBook.Worksheets("Benefit Export Query").Range("a1").Font.Size =
12
xlBook.Worksheets("Benefit Export Query").Range("a1").Font.Bold =
True
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").HorizontalAlignment = xlLeft
xlBook.Worksheets("Benefit Export
Query").rows("1:9999").VerticalAlignment = xlTop
xlBook.Worksheets("Benefit Export Query").rows("2:9999").WrapText
= True
xlBook.Worksheets("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