microsoft script editor

[chanman525]

Good afternoon experts,

I have an excel worksheet in which I use several MSQueries (in specific cells) to pull information from an access database.
I changed the location of the database and finally figured out how to go in to the script editor do a find / replace and change all the paths to my new database location but I would like to put the correct path in a cell in the worksheet and have ms script reference that as a variable. Following is a line of scipt that has the database path in it. Does anyone know how to use a variable that would reference a cell in my worksheet for this path? Thanks in advance for any help you can provide

<x:Connection>DSN=MS Access Database;DBQ=g:\ryano\E2Automation.mdb;DefaultDir=g:\ryano;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeo</x:Connection>

 

[ThomasLafferty]

Copy and paste your line of script into a cell of your worksheet.

Click Insert>Name>Define and name that cell something like myDBPath.

Here's some VB script which uses that name:

Sub GetDBPath()
On Error Goto Err_GetDBPath
    Dim strDBPath As String, Target As Range
    Application.Goto "myDBPath"
    Set Target = Selection
    strDBPath = Target.Value


Exit_GetDBPath:
     Exit Sub


Err_GetDBPath:
     Msgbox Err.Description
     Resume Exit_GetDBPath

End Sub

Hope this helps...
Tom

Live once die twice; live twice die once.

 

[chanman525]

Hi Tom,

I tried your suggestion but whenI pasted the sub you posted in the script, excel did not like it very well. It tunred your sub in to this (below). I obviously do not understand what I am doing. Could you educate me a little?

<DIV class=body>Sub GetDBPath()<BR>On Error Goto Err_GetDBPath<BR>    Dim
strDBPath As String, Target As Range<BR>    Application.Goto
"myDBPath"<BR>    Set Target = Selection<BR>    strDBPath =
Target.Value<BR><BR><BR>Exit_GetDBPath:<BR>     Exit
Sub<BR><BR><BR>Err_GetDBPath:<BR>     Msgbox Err.Description<BR>     Resume
Exit_GetDBPath<BR><BR>End Sub</DIV>

 

[ThomasLafferty]

This looks to me like html tags like you would see generated by VB.Net. Out of my league on this one.

Tom

Live once die twice; live twice die once.

 

[TonyJollans]

Let me first clear up the misunderstanding. You are asking about working with generated html in the script editor and Tom is talking about coding n VBA in the VB editor; two entirely different things.

What you see in the script editor is a representation, a different view if you like, of your workbook. It may be possible to add some code (javascript or vbscript) as you would in a web page but I don't know how, or even whether, Excel would maintain it or under what circumstances it would be run. I am really not familiar enough with it to comment further although my gut feeling is that you can't do what I think you want.

One of the Excel experts whom I'm sure will pass this way soon can probably tell you how to do it a different way.

Enjoy,
Tony

--------------------------------------------------------------------------------------------
We want to help you; help us to do it by reading this: Before you ask a question.

Professional Office Developers Association