VB in Excel

I just need excel to run the file user will not be clicking on the file it will be part of code.. That will call the file and run it..

I will try what you posted looks like it makes sense i just dont know the syntax.

Thanks

No did not work..

may this would help..

excel can open a command prompt enter the path and enter and close that will work too

if Excel cant just run the file

Have you determined wich program opens your hta file?

Can you post your call to the shell command?

I can call it from the command line simply typing the file name.. if i am in the folder where the file is obviously..

Thats just it I dont have any thing I want to create a VB method that runs this file..

Maybe this will help...heres the order of the process that is taking place
user presses a button in Excel
Button runs a macro that process data and saves it to an external csv file
Now macro will call a method that run HTA file...(heres where im stuck)
HTA file will start to process that csv file

I can run the hta file from the command line but how to I run the file from a macro in Excel..


I was thinking if VB can open a command window and have it run the hta file then close its self once its executed the HTA file....(Just a thought)

Hello Rob,

Yes, the method now open the file with this method

Public Sub ImportMAS90()

Shell "mshta.exe ""M:\MAS90\Home\MAS90.hta"""

End Sub


If I run it from the command line it works perfect... If I run it from the method in Excel I get an:
Error #12: File does not Exist (or already exist)
thats wierd??

Have you tried.

Shell "mshta.exe M:\MAS90\Home\MAS90.hta"

Nope sorry same error...

I dont understand.. What could it be the command line it works but when excel open the file it fails with this error?

Have you tried using FollowHyperlink?

I think that might work, but I've got to ask what you are doing with the hta file?

This worked for me.

ThisWorkbook.FollowHyperlink "C:\test.hta", NewWindow:=True

Though I did get a pop-up window asking me to confirm I wanted to open the file.

OK, what about
Shell "C:\Windows\System32\mshta.exe ""M:\MAS90\Home\MAS90.hta"""

Regards,

Rob.

Nothing!!

I think it is related to the problem we had when testing. Remember the batch file worked when run from ..\home. Try this, when you recieve the error 12, type "? lwd". No quotes. The result will show the path where ProvideX is running. Then we can explore solutions.
 

Wow miles  awsome  dude I'll check soon as I get to a computer!!

Actually, can I just confirm something.....when you get this error, is the error from Excel within the Sub, or does the HTA load, and the error is actually from within the HTA?

I have tested
Shell "mshta.exe ""M:\MAS90\Home\MAS90.hta"""

and it works fine in terms of loading the HTA.

So if the error is from within the HTA, perhaps you've got some code that refers to the working directory?  If so, try

ChDir "M:\MAS90\Home\"
Shell "mshta.exe ""M:\MAS90\Home\MAS90.hta"""

Regards,

Rob.

Rob your Correct as I posted above the HTA file works fine. I loads the login..

If I run hta from command line everything runs smooth..

If I Call the hta file from a command in excel thats when I have The issue..

RRmiles helped me with MAS90 and that error 12 is a MAS90 error.. I not on a computer now writing from phone..

I will test your command later today.. for sure!! Thank you for your assistance

Making sure you are starting from M:\mas90\home should solve the error 12 problem. You Should not need the hta. Just call it as we did in the code. .

Extra Tip: Some people like to put a button in Excel to start the VB code. I like to put it in the Workbook BefroreSave procedure. I dont like to depend on users to push buttons. I want to be sure when Excel is saved, my proceedure is run automatically.That way they should always in sync.

Extra Tip: Some people like to put a button in Excel to start the VB code. I like to put it in the Workbook BefroreSave procedure. I dont like to depend on users to push buttons. I want to be sure when Excel is saved, my proceedure is run automatically.That way they should always in sync.

Miles, The excel file is not on the same server as the MAS90 Directory.. I tried to "? lwd" but when I hit space the box closes.. And with out the space i get nothing................... Yes i do need the HTA to request a login and record whos doing the operation... The HTA file works perfect.. But that may be the issue the excel file is not in the directory..

Ok I tested the excel file in the Home directory and your correct it worked perfectly fine..

Rob I tried your command from the excel file in different directory and it still did not work.

That great but I really dont like the idea of regular users going on to the MAS90 server and getting files.. This file resides on the network but not on the mas90 server..

Is there any way where I can have the file on the network where users can get to it and it still work...

There must be a work around...

Thank to both of you for the assistance!!

Put the VIWI## file in the central network location along with the Excel file. The Vi job may run initially, but still get confused. Its worth a try anyway. It it doest not work, it is possible to write a bit of ProvideX code connected to the VI job to get to the MAS90 location. See your VI manual under Perform Logic. You wont be able to write it but you will understand what is happening. You will have to convince some kind soul to write it for you.

Put the VIWI## file in the central network location along with the Excel file. The Vi job may run initially, but still get confused. Its worth a try anyway. It it doest not work, it is possible to write a bit of ProvideX code connected to the VI job to get to the MAS90 location. See your VI manual under Perform Logic. You wont be able to write it but you will understand what is happening. You will have to convince some kind soul to write it for you.

I mean a Copy of the VIWI ## file right.. At the same path where my excel file is correct.. That's what I understood..

Worst case senario if it does not work
By any chance are you a Kind soul capable?? If I created another question??

I can test tomorrow!!

>> RRmiles helped me with MAS90 and that error 12 is a MAS90 error

If this is the case, then the HTA file is loading when the code is run from Excel, correct? It's just that you get the Error 12 when the MAS90 command is executed?

Is this the line that you have in the HTA?
prog = pvx + "\home\pvxwin32.exe  ..\soa\startup.m4p -arg DIRECT UION "&Username&" "& strPw &" SKT VIWI00"

Maybe what is wrong here is that this path does not contain absolute paths, and therefore a file cannot be found if it is run from a different location.  What happens if you make all file reference absolute, like:
prog = pvx + "\home\pvxwin32.exe  m:\pvxwin\soa\startup.m4p -arg DIRECT UION " & Username & " " &  strPw & " SKT VIWI00"

I would assume that the pvx varialbe already holds a full path before the \home\pvxwin32.exe bit??

Regards,

Rob.

Rob I tried your new Line file. and now get the error from the command line.. Now I get error even with out excel now it gives me the same error 12

FYI
The absolute path Miles is speaking of is

M:\MAS90\Home

Are you suggesting to remove the pvx value and replace it with a full path..

I have VS2008..maybe if i can out it thru a debugger I can see whats going on with the values.. Just seem to have hit a wall here..

Thank you guys for your assistance!!

Hi, I assume you changed this bit:
pvxwin32.exe  m:\pvxwin\soa\startup.m4p

so that it points to the proper path to startup.m4p

Perhaps though, seeing as you're not running this from the server itself, maybe the problem is that there is no client mapping (from the person that runs the Excel file) to find the file, as the program will look for the paths effectively from the client, not the server.

Is there any way you could try using UNC paths throughout, like in Excel, use:

Shell "mshta.exe ""\\server\share\MAS90\Home\MAS90.hta"""

and in the HTA, use:
\\server\share\home\pvxwin32.exe \\server\share\soa\startup.m4p -arg DIRECT UION <username> <password> SKT VIWI00

But, I'm not sure what the value is that is read from the registry.  Can you post an example of what is written in
HKLM\Software\ODBC\ODBC.INI\SOTAMAS90\Directory

Regards,

Rob.

Oh, and to output values, I tend to just use MsgBox's....so before
objShell.Run prog, 0, True

I would put
MsgBox "About to run " & vbCrLf & prog

and you'll see the value of the variable "prog" before it is executed.

Regards,

Rob.

OK. Here more Info.. I tried the new Line you gave me and it didnt even work from the command Line..

The value of Directory for SOTAMAS90 was M:\MAS90

The Images:
There are three images
1: The Top images is the message box I get when file is ran from the command Line

2: The Middle image is the message box I get when running from Excel.. (FYI I get the error 12 message First then the Message box)

3: This is a picture of the reg at the path you requested...


I also did the UNC path from method in excel..
and the Message box was just like the previous 2..

Thank you for your patience!!!

I also changes the Code with UNC path
 
Public Sub ImportMAS90()
 
Shell "mshta.exe ""\\gssrv08\MAS90\MAS90\Home\MAS90.hta"""
 
End Sub
                                              

1:
2:
3:
4:
5:
6:
7:

Sorry for the delay. I took Sunday off. Put the VI job in the folder where Excel starts. The problem with this is Excel is loaded by each machine locally. Therefore this can be a network nightmare. Yes I can do the code. If the error 12 box shows you a dialogue box that offers 3 buttons, select the one that will produce a Error debugging box in ProvideX. In the lower left corner there will be a button that is labled Debug. This will get the command console that you can type in ? LWD.

Away form my desk .. I will make a copy of that job to where the excel file resides..

FYI.. The error 12 message box only has 1 option.. "OK" is the only option I get

Change the command to just Providex without the rest of the command, then type ? LWD. This will not run the VI job but should avoid the error. To exit type BYE. We may also use an INI file for ProvideX, which will keep us from putting code into the VI job
 

WOW.... That its both you guys are awesome!!!

Thank you both for your patience I will raise point split the credit...

Both Experts were great!!