Pivot Table Code
 

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.

Pivot Table Code
 

Hi Martin,
You can create a string variable only for the string that is inside the
Array(Array(, that is for
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2"

Similarly you can have another string variable to store the following string
"048;PageTimeout=5;"
Hence if the two string variables are called str1 and str2 then the
connection statement can become
..Connection = Array(Array(str1),Array(str2))

You can code your access file name inside of str1. This is because Array is
function that returns an array whereas your code is treating it like a string.

Hope this helps.

Alok Joshi
"Martin" wrote:

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.

Pivot Table Code
 

Thanks Alok. That worked great!

Now I am getting a type mismatch error when I try to do the same thing to
the CommandText line. Here is the original line and my modifications:
ORIGINAL
..CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")

CommText = Chr(34) & "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(34) & _
Chr(13) & "" & Chr(10) & _
Chr(34) & "FROM `" & "D:\work\Projects\IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` & chr(34)"

.CommandText = Array(CommText)

I have tried the same without the imbedded double-quotes (Chr(34)'s) but get
the same error. I tried two variables with no success.

Thanks again.

"Alok" wrote:

Hi Martin,
You can create a string variable only for the string that is inside the
Array(Array(, that is for
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2"

Similarly you can have another string variable to store the following string
"048;PageTimeout=5;"
Hence if the two string variables are called str1 and str2 then the
connection statement can become
.Connection = Array(Array(str1),Array(str2))

You can code your access file name inside of str1. This is because Array is
function that returns an array whereas your code is treating it like a string.

Hope this helps.

Alok Joshi
"Martin" wrote:

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.

Pivot Table Code
 

Martin,
Looking at the Command String array, I find there are two strings and not one.
(Just double check) hence you may need code like this. Though it does not
make sense that the From and the path is part of one
string and the database name is part of the second string.

Dim commtext1$, commtext2$
commtext1 = "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & _
Chr(13) & "" & Chr(10) & _
"FROM `" & "D:\work\Projects\"

commtext2 = "IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` "

I regret I am not able to help you definitively.

Alok Joshi


"Martin" wrote:

Thanks Alok. That worked great!

Now I am getting a type mismatch error when I try to do the same thing to
the CommandText line. Here is the original line and my modifications:
ORIGINAL
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")

CommText = Chr(34) & "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(34) & _
Chr(13) & "" & Chr(10) & _
Chr(34) & "FROM `" & "D:\work\Projects\IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` & chr(34)"

.CommandText = Array(CommText)

I have tried the same without the imbedded double-quotes (Chr(34)'s) but get
the same error. I tried two variables with no success.

Thanks again.

"Alok" wrote:

Hi Martin,
You can create a string variable only for the string that is inside the
Array(Array(, that is for
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2"

Similarly you can have another string variable to store the following string
"048;PageTimeout=5;"
Hence if the two string variables are called str1 and str2 then the
connection statement can become
.Connection = Array(Array(str1),Array(str2))

You can code your access file name inside of str1. This is because Array is
function that returns an array whereas your code is treating it like a string.

Hope this helps.

Alok Joshi
"Martin" wrote:

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.

Pivot Table Code
 

Unfortunately, all I can say is that is what was recorded. I was also
confused by the underscore (which I believe is a line-continuation)
immediately followed by a comma which usually means that a second command
parameter follows. All this in the middle of the path and filename.

I tried your suggestion and got a bit farther. The CreatePivotTable command
which follows now produces a bad syntax error. I will do more playing to see
what I can come up with.

Thanks again.



"Alok" wrote:

Martin,
Looking at the Command String array, I find there are two strings and not one.
(Just double check) hence you may need code like this. Though it does not
make sense that the From and the path is part of one
string and the database name is part of the second string.

Dim commtext1$, commtext2$
commtext1 = "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & _
Chr(13) & "" & Chr(10) & _
"FROM `" & "D:\work\Projects\"

commtext2 = "IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` "

I regret I am not able to help you definitively.

Alok Joshi


"Martin" wrote:

Thanks Alok. That worked great!

Now I am getting a type mismatch error when I try to do the same thing to
the CommandText line. Here is the original line and my modifications:
ORIGINAL
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")

CommText = Chr(34) & "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(34) & _
Chr(13) & "" & Chr(10) & _
Chr(34) & "FROM `" & "D:\work\Projects\IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` & chr(34)"

.CommandText = Array(CommText)

I have tried the same without the imbedded double-quotes (Chr(34)'s) but get
the same error. I tried two variables with no success.

Thanks again.

"Alok" wrote:

Hi Martin,
You can create a string variable only for the string that is inside the
Array(Array(, that is for
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2"

Similarly you can have another string variable to store the following string
"048;PageTimeout=5;"
Hence if the two string variables are called str1 and str2 then the
connection statement can become
.Connection = Array(Array(str1),Array(str2))

You can code your access file name inside of str1. This is because Array is
function that returns an array whereas your code is treating it like a string.

Hope this helps.

Alok Joshi
"Martin" wrote:

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.

Pivot Table Code
 

I found the problem causing the syntax error. My fault. Your suggestion
worked
fine.

Thanks.


"Alok" wrote:

Martin,
Looking at the Command String array, I find there are two strings and not one.
(Just double check) hence you may need code like this. Though it does not
make sense that the From and the path is part of one
string and the database name is part of the second string.

Dim commtext1$, commtext2$
commtext1 = "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & _
Chr(13) & "" & Chr(10) & _
"FROM `" & "D:\work\Projects\"

commtext2 = "IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` "

I regret I am not able to help you definitively.

Alok Joshi


"Martin" wrote:

Thanks Alok. That worked great!

Now I am getting a type mismatch error when I try to do the same thing to
the CommandText line. Here is the original line and my modifications:
ORIGINAL
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")

CommText = Chr(34) & "SELECT `Quarterly Volume Query`.COUNTY_NAME, " & _
"`Quarterly Volume Query`.HOSPITAL_NAME, " & _
"`Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`, " & _
"`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(34) & _
Chr(13) & "" & Chr(10) & _
Chr(34) & "FROM `" & "D:\work\Projects\IHHA_Temp" & _
"`.`Quarterly Volume Query` `Quarterly Volume Query` & chr(34)"

.CommandText = Array(CommText)

I have tried the same without the imbedded double-quotes (Chr(34)'s) but get
the same error. I tried two variables with no success.

Thanks again.

"Alok" wrote:

Hi Martin,
You can create a string variable only for the string that is inside the
Array(Array(, that is for
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2"

Similarly you can have another string variable to store the following string
"048;PageTimeout=5;"
Hence if the two string variables are called str1 and str2 then the
connection statement can become
.Connection = Array(Array(str1),Array(str2))

You can code your access file name inside of str1. This is because Array is
function that returns an array whereas your code is treating it like a string.

Hope this helps.

Alok Joshi
"Martin" wrote:

I am new to using VB, so I usually record a script that will perform the task
I wish, then I modify it to be more "general" by substituting variables for
the portions that would change. This has worked well for me in the past, but
my latest task is causing problems.

I recorded the following that creates and Excel Pivot Table from a query
stored in an Access database. As it stands, it works just fine. However, I
would like to be able to change the Access file name and to create the pivot
table in the current workbook and current worksheet.

RECORDING START
With ActiveWorkbook.PivotCaches.Add(SourceType:=xlExter nal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;Defaul tDir=D:\work\Projects;DriverId=25;FIL=MS Access;MaxBufferSize=2" _
), Array("048;PageTimeout=5;"))
.CommandType = xlCmdSql
.CommandText = Array( _
"SELECT `Quarterly Volume Query`.COUNTY_NAME, `Quarterly Volume
Query`.HOSPITAL_NAME, `Quarterly Volume Query`.`DISCHARGE_DATE By Quarter`,
`Quarterly Volume Query`.`Count Of Inpatient`" & Chr(13) & "" & Chr(10) &
"FROM `D:\work\" _
, "Projects\IHHA_Temp`.`Quarterly Volume Query` `Quarterly Volume
Query`")
.CreatePivotTable TableDestination:="[prodline.xls]Sheet3!R6C1", _
TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
End With
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:=Array( _
"COUNTY_NAME", "HOSPITAL_NAME"), ColumnFields:="DISCHARGE_DATE By
Quarter"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count Of
Inpatient"). _
Orientation = xlDataField
RECORDING END

Starting with the Connection statement, I created a variable that contains
the text of everything after the "=" sign as follows:

ConnectText = "Array(Array(" & Chr(34) & _
"ODBC;DSN=MS Access Database;DBQ=" &
"D:\work\Projects\IHHA_Temp.mdb" & _
";DefaultDir=" & "D:\work\Projects" & _
";DriverId=25;FIL=MS Access;MaxBufferSize=2" & _
Chr(34) & Chr(41) & ", " & _
"Array(" & Chr(34) & "048;PageTimeout=5;" & _
Chr(34) & Chr(41) & Chr(41)

I then modified the Connection statement as follows:

.Connection = ConnectText


Every time I execute, this modified statement generates a "Runtime error
1004" error. I have tried using Dim statements to set this variable to
String or Variant to no affect.

Can someone explain what could be wrong?

Also, is the DefaultDir portion of the Connection statement related to the
Excel or Access file? I assume the Access file.


Thanks for the help.