Automate OLAP Cube Creation with Excel VBA, wrong datatype or syntax? 1

[bg0]

Using a macro recorder in Excel, I generated code for creating a Pivot Table based on an external database (comma delimited text file) and an OLAP Cube based on the data. Attempting to automate this code to accept any input data source, file name, and field queries I have replaced some arguments with variables.

For some of the parameters, I'm getting a type mismatch when I when I replace the argmuents with the variables by putting:
" + variant_variable + "
in place of the argument.

It worked for the Data Source parameter argument, but it doesn't seem to be working for the others. Any guidance would be highly appreciated.

 

[xlbo]

Can you post the code you are using and highlight the lines it errors on Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bg0]

'The following code segment works
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array( _
"OLEDB; Provider=MSOLAP; Initial Catalog=[OCWCube]; Data Source= " + Range("A1&quot + ";" _
, _
" CreateCube=CREATE CUBE [OCWCube] ( DIMENSION [F6], LEVEL [All] TYPE ALL, LEVEL [F6], DIMENS" _
, _
"ION [F5], LEVEL [All] TYPE ALL, LEVEL [F5], DIMENSION [F4], LEVEL [All] TYPE ALL, LEVEL [F4], DIMENSION [F3], " _
, _
" LEVEL [All] TYPE ALL, LEVEL [F3], MEASURE [Count Of F1] FUNCTION COUNT, MEASURE [Count Of F2] FUNCTION COUNT ); " _
, _
"InsertInto=INSERT INTO OCWCube([Count Of F1], [Count Of F2], [F3].[F3], [F4].[F4], [F5].[F5], [F6].[F6]) OPTIONS ATTEMPT_ANALYS" _
, _
"IS SELECT file1.F1, file1.F2, file1.F3, file1.F4, file1.F5, file1.F6 FROM file1.txt file1; Source_DSN=""DefaultDir=C:\Document" _
, _
"s and Settings\directory\My Documents;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxSc" _
, _
"anRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"";UseExistingFile=False" _
)
.CommandType = xlCmdCube
.CommandText = Array("OCWCube&quot
.MaintainConnection = True
.CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select


I have not been able to correctly replace the CreateCube argument with a variable (CubeDimension) holding the same string

With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExternal)
.Connection = Array( _
"OLEDB; Provider=MSOLAP; Initial Catalog=[OCWCube]; Data Source= " + Range("A1&quot + ";" _
, _
" CreateCube= " + CubeDimension + "; " _
, _
"InsertInto=INSERT INTO OCWCube([Count Of F1], [Count Of F2], [F3].[F3], [F4].[F4], [F5].[F5], [F6].[F6]) OPTIONS ATTEMPT_ANALYS" _
, _
"IS SELECT file1.F1, file1.F2, file1.F3, file1.F4, file1.F5, file1.F6 FROM file1.txt file1; Source_DSN=""DefaultDir=C:\Document" _
, _
"s and Settings\directory\My Documents;Driver={Microsoft Text Driver (*.txt; *.csv)};DriverId=27;FIL=text;MaxBufferSize=2048;MaxSc" _
, _
"anRows=8;PageTimeout=5;SafeTransactions=0;Threads=3;UserCommitSync=Yes;"";UseExistingFile=False" _
)
.CommandType = xlCmdCube
.CommandText = Array("OCWCube&quot
.MaintainConnection = True
.CreatePivotTable TableDestination:="", TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select

I've tried this with the CubeDimension variable both as a variant and as a string as well as putting it in a cell and using a "Range" reference, with and without the " + + " syntax and I've tried leaving the "CREATE CUBE [OCWCube] " part in the argument and just setting CubeDimension to the parenthetical statement. Let me know if more code or more cleaned up code would be helpful. (Sorry about the mess and thank you for any help)

 

[xlbo]

I think that there are 2 potential issues here.
1: The way the string is concatenated
2: The way " " are handled within string variables
Try setting a watch on Cubedimension and see what it evaluates to (as a string)
As far as I can see, you are dealing with the 2nd element in an array so the syntax should be:
"Createcube=" & CubeDimension & ";" , "InsertInto......"
I reckon it's your " throwing the string out. You may well need to replace " with """
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[xlbo]

In the CubeDimension string that is ;-) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bg0]

xlbo,
Thanks, all those ideas made sense and after checking it out and still having trouble, I simply tried editing the format of the working code segment to see if it would still work. For some reason it does not work when I change the 3 lines of code that are a part of the CreateCube parameter into 1 line of code. It does not give me a syntax error though.

The following does not work as the 1 line of code:
..., _
" CreateCube=CREATE CUBE [OCWCube] ( DIMENSION [F6], LEVEL [All] TYPE ALL, LEVEL [F6], DIMENSION [F5], LEVEL [All] TYPE ALL, LEVEL [F5], DIMENSION [F4], LEVEL [All] TYPE ALL, LEVEL [F4], DIMENSION [F3], LEVEL [All] TYPE ALL, LEVEL [F3], MEASURE [Count Of F1] FUNCTION COUNT, MEASURE [Count Of F2] FUNCTION COUNT ); " _
, _
"InsertInto=...

This next following segment works even though I put together the first two lines of the parameter code into one (changing the total from 3 lines to 2):

..., _
" CreateCube=CREATE CUBE [OCWCube] ( DIMENSION [F6], LEVEL [All] TYPE ALL, LEVEL [F6], DIMENSION [F5], LEVEL [All] TYPE ALL, LEVEL [F5], DIMENSION [F4], LEVEL [All] TYPE ALL, LEVEL [F4], DIMENSION [F3], " _
, _
" LEVEL [All] TYPE ALL, LEVEL [F3], MEASURE [Count Of F1] FUNCTION COUNT, MEASURE [Count Of F2] FUNCTION COUNT ); " _
, _
"InsertInto=...

This seems to point to your first idea, about the way the string is concatenated, but I am at a loss as to why I can't compress the code into one line. I believe I did not lose even one space in the reformatting so I'm very confused.
The answer to this would probably solve several of my problems that I had thought were all due to different reasons.
Thank you for all the help.

 

[xlbo]

Have you tested the CubeDimension string to see what it evaluates to ???
You can do this either by putting a watch on it or setting it to appear in a msgbox - this should help you get the concatenation and the " correct within the string Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bg0]

Yeah, that was a good diagnostic. I also had it output the CubeDimension string to a spreadsheet cell. There doesn't seem to be anything wrong with it in either diagnostic (although the watch that I added did not seem to show the entire string because there seems to be a limit to the character length in the window).

Besides, I found that all I had to do to mess up the execution was to take the working code segment and remove the line continuation characters and set the "CreateCube =..." statement to one line instead of three. Apparently, it can't handle being one line. I can compress it into two lines, but there appears to be a section that can't be connected on the same line of code. Since my CreateCube parameter will not always have the same length, I don't think it is an option for me to have it broken into two or three lines. I also can't break it into separate lines for every segment of the CreateCube statement.

Is it possibly a side effect of using a recorded macro that there can become formatting restrictions like the inability to compress the broken lines into one?

 

[xlbo]

I've never heard of that before. Generally, if there are line feeds etc, they can be removed and the whole shebang put on one line. There should be no reason why this isn't possible (although I have to say, my experience with generating OLAP cubes on the fly with VBA is limited to say the least) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[bg0]

Geoff,
Thank you for all your guidance. I finally decided to eliminate the "Array(...)" that the Connection was being set equal to and replaced it with the string of arguments. Maybe using the Array() would require some sort of change to a dynamic array for the proper character length. Anyhow it's working now, and you were still right about watching the string concatenation because even after I finally fixed the weird problem above, it had trouble handling my string variable. I found a comma where it wasn't supposed to be and that seems to be the last fix needed because it's working great now.

Thank you again for all your help, Geoff.
-Glen