Using MS Query into Excel.. Can I run external script? 1

[MCubitt]

I have an excel file which uses MS Query to refresh data.

Input is from a CSV file, using a MS Text ODBC connection.

It works fine.

However, the CSV file is created from a VBS script from a text file.

My question is, can I execute the VBS script from within the MS Query (probably not) and if so, can I keep it in tandem with all refreshes (automatic or use decided) ?

Otherwise I will have to ask the user to run the VBS script then refresh, which is not ideal really.

Thanks




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

MCubitt,

Why don't you run the script and then Refresh the query.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Skip,

Sorry for not making it clear, but the script will update the 2nd text file from the 1st, the MS Query uses the 2nd. The 1st text file changes every 1-10 minutes. So it is necessary for the script to run when the user refreshes. I could schedule the script to run every 30 seconds to a minute, but there may be ocassions when the user refreshes and EXPECTS their data to show. Does this make sense?




There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

YOUR MACRO WILL...
[tt]
1) Run the script to update the 2^nd file.

2) Refersh the Query on the 2^nd file.
[/tt]
or am I missing something still?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Oh.. a Macro! That was what I missed from your post!

So add a button (rather than use MS Query refresh/Excel data refresh). However, an added complciation is that I auto-refresh when a user changes values in a cell which are used as parameters. I presume I'd have to educate the user, but that's ok.



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Okay, now it gets embarassing... I never have used macros in Excel. How do I add a button so when the user clicks it, it runs a script which is on another server (a VBS file) and then refreshes the data?

Thank you


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

If that user can run it from their PC, then they can launch it in a macro. The only thing will be to test when the update process is done, most like by checking the status of the file being updated in a loop.

Does this user have access to that server?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Yes, via UNC (eg, \\keyprint\zfax\SERVER\Z-DB\script.vbs).

But I cannot even add a macro button and macro program! I looked un Google and still couldn't find how...


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

right click you Excel TOOLBAR and select Control Toolbox - and select Command Button icon and "place" it on you sheet.

With the new Command Button selected on your sheet, select the Properties icon on the Control Toolbox Toolbar. This displays all the properties of the selected control. Change the Caption property for the button and any other properties you like.

Double click the new Command Button. Now you are viewing the code sheet.

In the upper LH corner is a dropdown that has CommandButton1. If you click the dropdown, you can see other Objects. We want CommandButton1.

In the upper RH corner is a dropdown that has click. You can view the other Procedures for this Object. We want Click because it is the Click Event of CommandButtin1 that will run this procedure.

I will need the path and name of the file that is being updated by this script.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Skip, going very well so far, thanks! Excellent instructions.


The path I am using is
\\ifsprint\ZFAX\SERVER\Z-DB\ZetafaxEXTRACT.vbs

Thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

Sorry, that was me not reading.

The INPUT file is \\ifsprint\ZFAX\SERVER\Z-DB\fax.log

OUTPUT is \\ifsprint\ZFAX\SERVER\Z-DB\fax.csv

thanks



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

I think that this ought to work

Private Sub CommandButton1_Click()
    Dim f, fs
'run the script
    RetVal = Shell("\\keyprint\zfax\SERVER\Z-DB\script.vbs", 1)
    Set fs = CreateObject("Scripting.FileSystemObject")
'test f -- f will be NOTHING until fax.csv is released
    Set f = fs.GetFile("\\ifsprint\ZFAX\SERVER\Z-DB\fax.csv")
    Do While (f Is Nothing)
        DoEvents
    Loop
'now you're ready to run your macro
    YourOtherMacro
End Sub

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Thanks

Ok, I don't kow how to do code (I forgot, sorry).

I have this:
Private Sub Refresh_Click()
Dim f, fs
'run the script
RetVal = Shell("\\ifsprint\zfax\SERVER\Z-DB\ZetafaxEXTRACT.vbs", 1)
Set fs = CreateObject("Scripting.FileSystemObject")
'test f -- f will be NOTHING until fax.csv is released
Set f = fs.GetFile("\\ifsprint\ZFAX\SERVER\Z-DB\fax.csv")
Do While (f Is Nothing)
DoEvents
Loop
'now you're ready to run your macro
' YourOtherMacro
End Sub



But get Run-time error '5'. Debug highlights "RetVal = Shell("\\ifsprint\zfax\SERVER\Z-DB\ZetafaxEXTRACT.vbs", 1)".

And.. in YourOtherMacro... it's the standard Excel/MSQuery refresh, but I do not know how to call that.

Thanks


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

Private Sub Refresh_Click()
    Dim f, fs
'run the script
    RetVal = Shell("\\ifsprint\zfax\SERVER\Z-DB\ZetafaxEXTRACT.vbs", 1)
    Set fs = CreateObject("Scripting.FileSystemObject")
'test f -- f will be NOTHING until fax.csv is released
    Set f = fs.GetFile("\\ifsprint\ZFAX\SERVER\Z-DB\fax.csv")
    Do While (f Is Nothing)
        DoEvents
    Loop
'now you're ready to refresh your query
    ActiveSheet.QueryTables(1).Refresh
End Sub

Don't know on the Shell call

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

Try checking out this thread thread705-863066

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[MCubitt]

Thanks Skip, I will open a new issue because despite reading that thread, I still cannot resolve this problem. I tried removing the UNC and a specific drive, shortening the filename to 8 chars.

Thanks for your help thus far, really got me further than I was!



There's no need for sarcastic replies, we've not all been this sad for that long!

 

[MCubitt]

In the end I copied the entire vbs script into the macro. That works!


There's no need for sarcastic replies, we've not all been this sad for that long!

 

[SkipVought]

Great! Glad I could help in some way.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at faq222-2244