macro doesn't work on Excel for the mac but works on Windows XL??

[Dave F]

I just tried running a macro and got the following error:

"Run time error '1004': Methos 'Refresh' of object '_QueryTable'
failed"

This same macro runs fine on a Windows computer. The macro code in
question is:

Set qt = ActiveSheet.QueryTables.Add(Connection:=connectstring,
Destination:=ActiveSheet.Range("tickers").Offset(0, 1))

With qt
.Name = "T1"
.AdjustColumnWidth = False
.RefreshStyle = xlOverwriteCells
.RefreshOnFileOpen = False
.Refresh
End With

Ideas?

 

[Jon Peltier]

Excel on a Mac uses the VBA equivalent of Excel 97. In Excel 97, query
tables do not have the property AdjustColumnWidth (it was added in Excel
2000), causing the code to fail on the Mac.

- Jon

 

[gimme_this_gimme_that]

Excel on the Mac doesn't use Excel 97. Come on.

What's going on is that Excel on the Mac does not support the object
"ActiveSheet.QueryTables".

There are also no DAO libraries available for Excel on the Mac.

The way you handle queries on the Mac is to use Microsoft Query. You
stick a SQL statement into Microsoft Query and, assuming you have a
valid ODBC connection, it will place the result of that query into an
Excel Worksheet.

You parse that worksheet and read it as if it were a SQL record set
using VBA.

Note that you can not use the recorder and run Microsoft Query and then
use or modify the recorded code. It's not available.

If you need to read data from a database and format it, without using a
Microsoft Query step by a knowledgible user, then Excel isn't the tool
for the job.

 

[Jon Peltier]

Excel on the Mac doesn't use Excel 97. Come on.

Read what I wrote. VBA for the Mac has not progressed beyond the version of
VBA used in Office 97. Any enhancements implemented in Office 2000 or later
will not work on a Mac. I looked up "What's new in Excel 2000 VBA" and found
that QueryTables.AdjustColumnWidth was available in 2000 but not 97, so I
nominated it as a likely candidate. You've verified that not only
..AdjustColumnWidth but all of QueryTables is not available on the Mac.

- Jon