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.

 

[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?

 

[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

.