Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro doesn't work on Excel for the mac but works on Windows XL??
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
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro doesn't work on Excel for the mac but works on Windows XL??
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? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro doesn't work on Excel for the mac but works on Windows XL??
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
macro doesn't work on Excel for the mac but works on Windows XL??
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 ------- Jon Peltier, Microsoft Excel MVP Tutorials and Custom Solutions http://PeltierTech.com _______ wrote in message ups.com... 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
autofilter problem in Mac, but works in Windows, Excel 2003 | Excel Discussion (Misc queries) | |||
Macro works Macro does not work | Excel Discussion (Misc queries) | |||
macro which works in excel 2000 does not work in excel 2002 | Excel Programming | |||
Simple Macro, works in Excel 2002, 2003 but won't work in 2000 | Excel Programming | |||
Automation error -2147417848 on windows 98 while using excel in VB ( works fine in XP) | Excel Programming |