Editing Excel Workbook Connections to Web

[brooksc29]

--------------------------------------------------------------------------------

I am looking to make a slight change in the URL address for my data
connection to the web. However, when I open up the properties for each of the
connections (I have many, each on different worksheets), and click on
definition, the connection string is faded so that I cannot edit it.

Am I up the creek without a paddle here?

There is an option to edit the query at the bottom left of the definition
tab and after pressing it my web browser opens, however I am looking to edit
the web URL manually.

in the hope of being more specific for anyone who can help, here's exactly
what I'm trying to do...

this is the current URL for my data connection:

http://www.statspass.com/data/mlb/st...t=31&spText=vs
Lefthanders&time=0&sum=0&qual=&cust=&custTot=&tp=0&gmtyp=1&sea=2009&pos=&cty=&st=&bt=&thr=&cur=13&cu
rtxt=Current Texas
Rangers&rook=&tm=&tmtxt=&lg=&lgtxt=&mode=11_11&ord=DESC&cnt=all

I simply copied it from the web browser before importing the data.

all I am trying to do it is change the "2009" to "2010".

However, after finding where the connection string is stored and not being
able to edit it, I am at a loss as to how to edit the URL.

 

[joel]

Excel doesn't allowing editing the connection string. You have t
create a new connection. You can edit other properties like the Comman
Text. So you can go to the original connection and open a Query edito
and copy the old command text and paste to the new command text. th
command Text portion of a query is really the SQL. I can explain th
details if you need to do this.

 

[brooksc29]

Thanks for answering Joel.

If you could go into that a little further I would really appreciate it.
What is the Command Text you are talking about exactly?

 

[joel]

I recorded a macro while I manually set up a database query and here is
what I got (I made some modification so the posting was easier to read)



VBA Code:
--------------------


With ActiveSheet.QueryTables.Add( _
Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;DriverId=25;" & _
"FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"), _
Array(";")), _
Destination:=Range("A1"))

.CommandText = Array( _
"SELECT MSysAccessObjects.Data, " & _
"MSysAccessObjects.ID" & vbCrLf & _
"FROM `C:\temp\submission`.MSysAccessObjects MSysAccessObjects")

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
--------------------





The connection part of the above code cannot be changed which is the
file you are getting the data from. The other parameters are ediible
using the microsoft Query editor.

To get to the editor you have to first select any cell in the returned
query otherwise the menu option is grayed out. then Go to the menu
where you mornally add a query. In 2003 it is in the menu:

Data - Import External Data - Edit Query

Inside the Query editor you can find all the parameter above (except
the connection). The command text is inside the SQL button (or menu
option).

So what you have to do is setup up a new query to the new file and then
set the parameters that same as the old query. I may be easier to just
start from scratch. Sometimes people don't know the old query
parameters and need to look at the old query before a new query is
created.

 

[brooksc29]

Joel,

I really appreciate the work you've done to help, but I think I'm a little
confused as to if this is what I'm looking for to solve my problem.

The data I am importing is from the Web. In Excel 2007, it automatically
stores each import query in the Connection Properties under the Data tab.
When I open the workbook and enable macros, it will allow me to refresh all
the import queries after establishing a connection to the Web.

However, in the Connection Properties under the Definition Tab, the
Connection String box looks to be where the URL or SQL (I'm not sure I know
the difference) is stored.

I've been searching around for websites that might be able to answer this,
and the only thing I've found is something to do with creating a "trusted
folder" or "trusted sites". I'm not sure if the site and folder isn't
already trusted, how to change it if it's not, or if it even matters at all.

As far as your macro goes, is that written for refreshing the data import?
What exactly is it doing?

Also, what exactly would I be referring to when talking about parameters?

Finally, I know it would be much easier to start from scratch, however the
small change I need to make in the URL will actually create a URL for a Web
page that doesn't exist yet. It will in about a month, however that's why I
can't run the query on my own now. The only piece of URL code that will be
different is the 2010, so that's why I was hoping to do some leg work now and
have it work later when the web page is created.

Again, I really do appreciate your help. I hope I am not running you around
in circles.

I recorded a macro while I manually set up a database query and here is
what I got (I made some modification so the posting was easier to read)


VBA Code:

With ActiveSheet.QueryTables.Add( _
Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;DriverId=25;" & _
"FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"), _
Array(";")), _
Destination:=Range("A1"))

.CommandText = Array( _
"SELECT MSysAccessObjects.Data, " & _
"MSysAccessObjects.ID" & vbCrLf & _
"FROM `C:\temp\submission`.MSysAccessObjects MSysAccessObjects")

.Name = "Query from MS Access Database"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

--------------------





The connection part of the above code cannot be changed which is the
file you are getting the data from. The other parameters are ediible
using the microsoft Query editor.

To get to the editor you have to first select any cell in the returned
query otherwise the menu option is grayed out. then Go to the menu
where you mornally add a query. In 2003 it is in the menu:

Data - Import External Data - Edit Query

Inside the Query editor you can find all the parameter above (except
the connection). The command text is inside the SQL button (or menu
option).

So what you have to do is setup up a new query to the new file and then
set the parameters that same as the old query. I may be easier to just
start from scratch. Sometimes people don't know the old query
parameters and need to look at the old query before a new query is
created.


--
joel
------------------------------------------------------------------------
joel's Profile: 229
View this thread: http://www.thecodecage.com/forumz/showthread.php?t=181145

Microsoft Office Help

.

 

[joel]

The URL is the Http://... the you normally would see in your we
browser. It is equivalent to a filename. The is really no differenc
to excel if the data comes from either a file or a URL it is just
different pathname to get to the data.

Connection:=Array(Array( _
"ODBC;DSN=MS Access Database;" & _
"DBQ=C:\temp\submission.mdb;" & _
"DefaultDir=C:\temp;DriverId=25;" & _
"FIL=MS Access;MaxBufferSize=2048;PageTimeout=5"), _
Array(";")), _
Destination:=Range("A1"))

The filename is C:\temp\submission.mdb. If it was a webpage it woul
have a Http:// at the beginning of the string


The SQL is the filters to extract only the data you want from the dat
source. In the example I posted it is called the "Command_Text"


.CommandText = Array( _
"SELECT MSysAccessObjects.Data, " & _
"MSysAccessObjects.ID" & vbCrLf & _
"FROM `C:\temp\submission`.MSysAccessObjects MSysAccessObjects")


The Acces table is "MSysAccessObjects" and the columns I'm returning i
"Data" and "ID"


When I do the same thing on a webpage it looks similar

With ActiveSheet.QueryTables.Add( _

Connection:="URL;http://apps.cnbc.com/company/quote/index.asp?symbol=IBM"
_
Destination:=Range("A1"))
.Name = "index.asp?symbol=IBM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With


the connection is

Connection:="URL;http://apps.cnbc.com/company/quote/index.asp?symbol=IBM"

A webpage can have multuple tables. In this case I downloaded th
entire webpage. Again the connection can't be changed. The only rea
usful item that you want to sve is the Destination Location and th
refresh period.

I could write a macro that went through an entire workbook an
automtically deleted each query and add a new query with the sam
properties. I would probably make a copy of the original workbook
deleted the query from the copy and then added a new query using th
original workbook as a template.