I'm writing some code to create pivot tables from an SQL database that was
created several years ago. Unfortunately, the person that created the
database used table names and field names with lots of spaces in them. For
starters, I have no opportunity to change the names. I've recorded the
macro below to create the pivot and select the source data. However, I'd
like to clean it up a bit for two reasons:
1) Make it easier to read so that field names are split on multiply lines
and
2) Add variables for the server and database names so I can use an input box
to change these at the start. (The server in the example is
"JMICHL_03\SQL2000" and the database is "POSData".

My problem is that when I try to edit the code below I can't seem to get the
the quotes in proper places so as to not generate errors. In addition, I
can't seem to figure out how to put a variable into this string that would
substitute a new server or database name. Could someone edit the code below
enough to give me an idea of where the quotes should be?

If it isn't obvious 'Customer Period / Type' is a table within the
database.

Thanks! - John

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT ""Customer Period / Type"".""Sales Organization ID"",
""Customer Period / Type"".""Period ID"", ""Customer Period /
Type"".""Customer Type"", ""Customer Period / Type"".""Current Sales Org
Decile"", ""Customer Period / Type"".""Prior " _
, _
"Revenue Centile""" & Chr(13) & "" & Chr(10) & "FROM
""POSData"".dbo.""Customer Period / Type"" ""Customer Period / Type"",
""POSData"".dbo.""Sales Organizations"" ""Sales Organizations""" & Chr(13) &
"" & Chr(10) & "WHERE ""Sales Organizations"".ID = ""C" _
, "ustomer Period / Type"".""Sales Organization ID"""),
Connection:=Array( _
Array( _
"ODBC;DRIVER=SQL
Server;SERVER=JMICHL_03\SQL2000;UID=JMichl;APP=Mic rosoft Office
XP;WSID=JMICHL_03;DATABASE=POSData;Trusted_" _
), Array("Connection=Yes"))

John

SourceData and Connection are strings. Excel puts them into arrays for
reasons unbeknownst to me. I always thought it was in case the SQL was more
than 255 characters, but yours is and it still only uses one array. To deal
with quotes inside of strings, you have to use two double quotes. So
everything from SELECT to the end of the SQL statement is one string and any
quotes you want inside that have to be two double quotes.

From the immediate window

?""""
"
?chr(34)
"
?"The word ""quotes"" is in ""quotes"""
The word "quotes" is in "quotes"

Obviously I can't test this, but here's how I would format it:

Dim ServName As String
Dim DbName As String
Dim SrcData As String
Dim ConnStr As String

ServName = InputBox("Enter server name")
DbName = InputBox("enter database name")

SrcData = "SELECT " & _
"""Customer Period / Type"".""Sales Organization ID""," & _
"""Customer Period / Type"".""Period ID""," & _
"""Customer Period / Type"".""Customer Type""," & _
"""Customer Period / Type"".""Current Sales OrgDecile""," & _
"""Customer Period / Type"".""Prior Revenue Centile""" & _
Chr(13) & "" & Chr(10) & _
"FROM """ & DbName & """.dbo.""Customer Period / Type"" " & _
"""Customer Period / Type""," & _
"""" & DbName & """.dbo.""Sales Organizations"" " & _
"""Sales Organizations""" & _
Chr(13) & "" & Chr(10) & _
"WHERE ""Sales Organizations"".ID = " & _
"""Customer Period / Type"".""Sales Organization ID"""

ConnStr = "ODBC;DRIVER=SQL Server;SERVER=" & ServName & _
";UID=JMichl;APP=Microsoft Office XP;WSID=JMICHL_03;" & _
"DATABASE=" & DbName & ";Trusted_"

ActiveSheet.PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=SrcData, _
Connection:=Array(Array(ConnStr), Array("Connection=Yes"))


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"John Michl" wrote in message
...
I'm writing some code to create pivot tables from an SQL database that was
created several years ago. Unfortunately, the person that created the
database used table names and field names with lots of spaces in them.
For
starters, I have no opportunity to change the names. I've recorded the
macro below to create the pivot and select the source data. However, I'd
like to clean it up a bit for two reasons:
1) Make it easier to read so that field names are split on multiply lines
and
2) Add variables for the server and database names so I can use an input
box
to change these at the start. (The server in the example is
"JMICHL_03\SQL2000" and the database is "POSData".

My problem is that when I try to edit the code below I can't seem to get
the
the quotes in proper places so as to not generate errors. In addition, I
can't seem to figure out how to put a variable into this string that would
substitute a new server or database name. Could someone edit the code
below
enough to give me an idea of where the quotes should be?

If it isn't obvious 'Customer Period / Type' is a table within the
database.

Thanks! - John

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT ""Customer Period / Type"".""Sales Organization ID"",
""Customer Period / Type"".""Period ID"", ""Customer Period /
Type"".""Customer Type"", ""Customer Period / Type"".""Current Sales Org
Decile"", ""Customer Period / Type"".""Prior " _
, _
"Revenue Centile""" & Chr(13) & "" & Chr(10) & "FROM
""POSData"".dbo.""Customer Period / Type"" ""Customer Period / Type"",
""POSData"".dbo.""Sales Organizations"" ""Sales Organizations""" & Chr(13)
&
"" & Chr(10) & "WHERE ""Sales Organizations"".ID = ""C" _
, "ustomer Period / Type"".""Sales Organization ID"""),
Connection:=Array( _
Array( _
"ODBC;DRIVER=SQL
Server;SERVER=JMICHL_03\SQL2000;UID=JMichl;APP=Mic rosoft Office
XP;WSID=JMICHL_03;DATABASE=POSData;Trusted_" _
), Array("Connection=Yes"))

Thanks, Dick. It worked like a charm. I'll need to review it several times
so I absorb the placement of all of the quotes but this gives me a great
template to work with.

- John


"Dick Kusleika" wrote in message
...
John

SourceData and Connection are strings. Excel puts them into arrays for
reasons unbeknownst to me. I always thought it was in case the SQL was
more
than 255 characters, but yours is and it still only uses one array. To
deal
with quotes inside of strings, you have to use two double quotes. So
everything from SELECT to the end of the SQL statement is one string and
any
quotes you want inside that have to be two double quotes.

From the immediate window

?""""
"
?chr(34)
"
?"The word ""quotes"" is in ""quotes"""
The word "quotes" is in "quotes"

Obviously I can't test this, but here's how I would format it:

Dim ServName As String
Dim DbName As String
Dim SrcData As String
Dim ConnStr As String

ServName = InputBox("Enter server name")
DbName = InputBox("enter database name")

SrcData = "SELECT " & _
"""Customer Period / Type"".""Sales Organization ID""," & _
"""Customer Period / Type"".""Period ID""," & _
"""Customer Period / Type"".""Customer Type""," & _
"""Customer Period / Type"".""Current Sales OrgDecile""," & _
"""Customer Period / Type"".""Prior Revenue Centile""" & _
Chr(13) & "" & Chr(10) & _
"FROM """ & DbName & """.dbo.""Customer Period / Type"" " & _
"""Customer Period / Type""," & _
"""" & DbName & """.dbo.""Sales Organizations"" " & _
"""Sales Organizations""" & _
Chr(13) & "" & Chr(10) & _
"WHERE ""Sales Organizations"".ID = " & _
"""Customer Period / Type"".""Sales Organization ID"""

ConnStr = "ODBC;DRIVER=SQL Server;SERVER=" & ServName & _
";UID=JMichl;APP=Microsoft Office XP;WSID=JMICHL_03;" & _
"DATABASE=" & DbName & ";Trusted_"

ActiveSheet.PivotTableWizard _
SourceType:=xlExternal, _
SourceData:=SrcData, _
Connection:=Array(Array(ConnStr), Array("Connection=Yes"))


--
Dick Kusleika
MVP - Excel
www.dicks-clicks.com
Post all replies to the newsgroup.

"John Michl" wrote in message
...
I'm writing some code to create pivot tables from an SQL database that
was
created several years ago. Unfortunately, the person that created the
database used table names and field names with lots of spaces in them.
For
starters, I have no opportunity to change the names. I've recorded the
macro below to create the pivot and select the source data. However,
I'd
like to clean it up a bit for two reasons:
1) Make it easier to read so that field names are split on multiply
lines
and
2) Add variables for the server and database names so I can use an input
box
to change these at the start. (The server in the example is
"JMICHL_03\SQL2000" and the database is "POSData".

My problem is that when I try to edit the code below I can't seem to get
the
the quotes in proper places so as to not generate errors. In addition,
I
can't seem to figure out how to put a variable into this string that
would
substitute a new server or database name. Could someone edit the code
below
enough to give me an idea of where the quotes should be?

If it isn't obvious 'Customer Period / Type' is a table within the
database.

Thanks! - John

ActiveSheet.PivotTableWizard SourceType:=xlExternal,
SourceData:=Array( _
"SELECT ""Customer Period / Type"".""Sales Organization ID"",
""Customer Period / Type"".""Period ID"", ""Customer Period /
Type"".""Customer Type"", ""Customer Period / Type"".""Current Sales Org
Decile"", ""Customer Period / Type"".""Prior " _
, _
"Revenue Centile""" & Chr(13) & "" & Chr(10) & "FROM
""POSData"".dbo.""Customer Period / Type"" ""Customer Period / Type"",
""POSData"".dbo.""Sales Organizations"" ""Sales Organizations""" &
Chr(13)
&
"" & Chr(10) & "WHERE ""Sales Organizations"".ID = ""C" _
, "ustomer Period / Type"".""Sales Organization ID"""),
Connection:=Array( _
Array( _
"ODBC;DRIVER=SQL
Server;SERVER=JMICHL_03\SQL2000;UID=JMichl;APP=Mic rosoft Office
XP;WSID=JMICHL_03;DATABASE=POSData;Trusted_" _
), Array("Connection=Yes"))