Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Pivot Table Code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 336
Default 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.
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 318
Default 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.
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 336
Default 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.
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 318
Default 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.
Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 336
Default 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.
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 336
Default 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.
Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Excel Pivot Table VB Code carolini Excel Discussion (Misc queries) 1 February 6th 06 09:33 PM
Pivot Table Code FA Excel Discussion (Misc queries) 6 October 11th 05 04:03 PM
VBA Code for a pivot table to open database and to reference table current page Pete Straman Straman via OfficeKB.com Excel Programming 0 February 21st 05 03:57 AM
vba code for Pivot Table Paul Excel Programming 0 May 31st 04 05:04 AM
HELP! Code for Pivot Table Sandy[_3_] Excel Programming 6 September 3rd 03 09:43 PM


All times are GMT +1. The time now is 09:39 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement