Pivot Table Code

G

Guest

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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
 
G

Guest

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;DefaultDir=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 said:
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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
Click to expand...
 
G

Guest

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 said:
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;DefaultDir=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 said:
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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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;DefaultDir=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 said:
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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
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 said:
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;DefaultDir=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
:

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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

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

Thanks.


Alok said:
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 said:
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 said:
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;DefaultDir=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
:

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:=xlExternal)
.Connection = Array(Array( _
"ODBC;DSN=MS Access
Database;DBQ=D:\work\Projects\IHHA_Temp.mdb;DefaultDir=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.
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Excel getting info from Access 1
Error Trapping when getting external Data 2
Import external data from Access query with Variable 1
Not able to declare variable in MS Query. 2
CreatePivotTable: Invalid procedure call or argument 4
Macro to import external data from Access query to Excel 1
Changing string input in a query within a macro 2
variable for path name in ODBC connection 2

Top