difference between XL VBA on Windows and the Mac???
 

1. Dave F
View profile
More options Dec 26 2006, 2:16 pm
Newsgroups: microsoft.public.excel.misc
From: "Dave F"
Date: 26 Dec 2006 11:16:46 -0800
Local: Tues, Dec 26 2006 2:16 pm
Subject: difference between XL VBA on Windows and the Mac???
Reply to author | Forward | Print | Individual message | Show original |
Report this message | Find messages by this author
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:=connectstr ing,
Destination:=ActiveSheet.Range("tickers").Offset(0 , 1))

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

Ideas?


2. Jon Peltier
View profile
More options Dec 26 2006, 2:37 pm
Newsgroups: microsoft.public.excel.misc
From: "Jon Peltier"
Date: Tue, 26 Dec 2006 14:37:23 -0500
Local: Tues, Dec 26 2006 2:37 pm
Subject: difference between XL VBA on Windows and the Mac???
Reply to author | Forward | Print | Individual message | Show original |
Report this message | Find messages by this author
As answered in .programming:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Dave F" wrote in message

ups.com...

- Hide quoted text -
- Show quoted text -
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:=connectstr ing,
Destination:=ActiveSheet.Range("tickers").Offset(0 , 1))

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

Ideas?



3. Dave F
View profile
More options Dec 26 2006, 3:02 pm
Newsgroups: microsoft.public.excel.misc
From: "Dave F"
Date: 26 Dec 2006 12:02:37 -0800
Local: Tues, Dec 26 2006 3:02 pm
Subject: difference between XL VBA on Windows and the Mac???
Reply to author | Forward | Print | Individual message | Show original |
Report this message | Find messages by this author
Thanks, Jon.

Unfortunately I didn't see a response to my post in the .programming
newsgroup.

Dave

- Hide quoted text -
- Show quoted text -
Jon Peltier wrote:
As answered in .programming:

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
http://PeltierTech.com
_______

"Dave F" wrote in message
ups.com...
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:=connectstr ing,
Destination:=ActiveSheet.Range("tickers").Offset(0 , 1))

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

Ideas?

Hi,

It appears that .AdjustColumnWidth is supposed to work on the Mac.
Excel's help discusses it, but the command appears to have no effect
when I tried it. The command AdjustColumnWidth does not produce any
error, so no harm is done.

The AutoFormat property may accomplish what you are seeking. Just add
this to the list in With qt
.HasAutoFormat = True

The List Manager has to be "off" in order to use this command. Use a
form of this statement to turn the List Manager off if it is already on:
ActiveSheet.ListObjects("List1").ConvertToRange

-Jim

--
Jim Gordon
Mac MVP

MVPs are independent experts who are not affiliated with Microsoft.
http://mvp.support.microsoft.com/