| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
CommandText
Hi,
I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
Shouldn't you qualify PivotCache(1)
-- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
qualify?
do you mean: With Workbooks("Report Template.xls").PivotCaches(1) .CommandType = xlCmdSql .CommandText = Array("SELECT ACCOUNT_NO, ACCOUNTING_DATE, AMOUNT, APPROVAL, BASE_CURR_AMOUNT, CHART_ID, ", _ etc... "Tom Ogilvy" wrote: Shouldn't you qualify PivotCache(1) -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
Yes, that is what I mean.
-- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... qualify? do you mean: With Workbooks("Report Template.xls").PivotCaches(1) .CommandType = xlCmdSql .CommandText = Array("SELECT ACCOUNT_NO, ACCOUNTING_DATE, AMOUNT, APPROVAL, BASE_CURR_AMOUNT, CHART_ID, ", _ etc... "Tom Ogilvy" wrote: Shouldn't you qualify PivotCache(1) -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
that is what I had when I got the error... still trying things... no success
yet... "Tom Ogilvy" wrote: Yes, that is what I mean. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... qualify? do you mean: With Workbooks("Report Template.xls").PivotCaches(1) .CommandType = xlCmdSql .CommandText = Array("SELECT ACCOUNT_NO, ACCOUNTING_DATE, AMOUNT, APPROVAL, BASE_CURR_AMOUNT, CHART_ID, ", _ etc... "Tom Ogilvy" wrote: Shouldn't you qualify PivotCache(1) -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
figured it out...!
the PivotCache I was trying to update had multiple PivotTables using it as the source. When I fed the SQL into the PivotCache of a PivotTable without any dependant PivotTables, it worked!!!! "Simon Shaw" wrote: that is what I had when I got the error... still trying things... no success yet... "Tom Ogilvy" wrote: Yes, that is what I mean. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... qualify? do you mean: With Workbooks("Report Template.xls").PivotCaches(1) .CommandType = xlCmdSql .CommandText = Array("SELECT ACCOUNT_NO, ACCOUNTING_DATE, AMOUNT, APPROVAL, BASE_CURR_AMOUNT, CHART_ID, ", _ etc... "Tom Ogilvy" wrote: Shouldn't you qualify PivotCache(1) -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
Perhaps you aren't being clear. If a pivotcache doesn't have any dependent
pivot tables and you save the workbook, it will be deleted. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... figured it out...! the PivotCache I was trying to update had multiple PivotTables using it as the source. When I fed the SQL into the PivotCache of a PivotTable without any dependant PivotTables, it worked!!!! "Simon Shaw" wrote: that is what I had when I got the error... still trying things... no success yet... "Tom Ogilvy" wrote: Yes, that is what I mean. -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... qualify? do you mean: With Workbooks("Report Template.xls").PivotCaches(1) .CommandType = xlCmdSql .CommandText = Array("SELECT ACCOUNT_NO, ACCOUNTING_DATE, AMOUNT, APPROVAL, BASE_CURR_AMOUNT, CHART_ID, ", _ etc... "Tom Ogilvy" wrote: Shouldn't you qualify PivotCache(1) -- Regards, Tom Ogilvy "Simon Shaw" <simonATsimonstoolsDOTcom wrote in message ... Hi, I am trying to pass a String of SQL code into Pivotcache(1).CommandText = Array (...., ..., ..., etc) I keep getting the error message: Run-time error '1004' Application-defined or object error. clicking Help doesn't tell me anything. I have successfully passed SQL into the CommandText before, I thought I got it to work by limiting the length of each element of the array... When I take the SQL I am trying to pass in to the CommandText and paste it directly into MS Query behind the PivotTable it works fine. so I know the SQL code is valid. Ideas? Thanks Simon Shaw |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
CommandText
How did you removed the PivotTable dependancies? I have the same error.
Thanx Nick |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| CommandText Property of importing external data | Excel Programming | |||
| Setting CommandText property of PivotCache fails if cache has 1 PivotTable | Excel Programming | |||
| CommandText Property | Excel Programming | |||
| change commandtext in pivotcaches | Excel Programming | |||
Linear Mode
