By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,952 Members | 949 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,952 IT Pros & Developers. It's quick & easy.

Convert VBScript statement to VB.Net

BHo15
100+
P: 135
Hi.

I'm neither accomplished at vb.net or vbscript, but have been fooling with both of them as of late. I have a vb.net GUI that calls 2 vbscripts, and I would like to cut it down to just one (my skill level is not such that I could even attempt to convert the big one to vb.net).

The smaller of the 2 vbscripts just calls the larger one by running a command line statement. I wonder if someone can help me get started in getting this to vb.net. Here is the statement...

Expand|Select|Wrap|Line Numbers
  1. Set oShell = CreateObject("WScript.Shell")
  2.  
  3. If Not WScript.FullName = CurrentPath & "cscript.exe" Then
  4.     oShell.Run "cmd.exe /c" & WScript.Path _
  5.         & "\cscript.exe //NOLOGO " & Chr(34) _
  6.         & "TweakedFetch.vbs" & Chr(34) & " " & Chr(34) _
  7.         & strEmail & Chr(34) & " " & Chr(34) _
  8.         & strNetwork & Chr(34) & " " & Chr(34) _
  9.         & BDate & Chr(34) & " " & Chr(34) _
  10.         & EDate & Chr(34) & " " & Chr(34) _
  11.         & strP & Chr(34) & " >DNLD.csv"
  12.     WScript.Quit 0
  13. End If
I have no problem passing the variables to the vbscript. My problems come in dealing with the oshell, WScript, CScript, and the sending results to DNLD.csv.

Anyone willing to take a stab at it?

Thanks,
Brad
Feb 4 '14 #1
Share this Question
Share on Google+
20 Replies


BHo15
100+
P: 135
Well... I made some progress.

I used Dim oShell = CreateObject("WScript.Shell"), and then left off the If statement and the WScript.Quit. The rest of it works, except...

The & " >DNLD.csv" does not work. It is just printing results to the cmd window.

So how do I send the results to the DNLD.csv?

Thanks,
Brad
Feb 4 '14 #2

100+
P: 299
Looks to me like you should specify a drive and/or directory in front of DNLD.csv (example: C:\TestFolder\DNLD.csv). I also know that you state you aren't very proficient in neither VBS nor VB.NET, but anything that you can do in a VBScript, you can do in VB.NET. So, it's worth your time doing the research and creating only one program in VB.NET to carry out what the VBScripts do, rather than calling scripts from a VB.NET program. Please feel free to post as many questions as you'd like (as new questions, not in this thread) and I'd be more than happy to help.
Feb 4 '14 #3

BHo15
100+
P: 135
Thanks for the insight. Here is what I tried...

Expand|Select|Wrap|Line Numbers
  1.  Dim CurrentPath As String = _
  2.                        Application.StartupPath & "\"
  3.        Dim oShell = CreateObject("WScript.Shell")
  4.  
  5. oShell.Run "cmd.exe /c" & WScript.Path _
  6.         & "\cscript.exe //NOLOGO " & Chr(34) _
  7.         & "TweakedFetch.vbs" & Chr(34) & " " & Chr(34) _
  8.         & strEmail & Chr(34) & " " & Chr(34) _
  9.         & strNetwork & Chr(34) & " " & Chr(34) _
  10.         & BDate & Chr(34) & " " & Chr(34) _
  11.         & EDate & Chr(34) & " " & Chr(34) _
  12.         & strP & Chr(34) & " >" & CurrentPath &   
  13.                                       "DNLD.csv"
But... to no avail. Still just prints in CMD window.

Once we get this done... I'll be visiting more with you about the complete conversion to VB.NET. I really would like to do it that way.
Feb 4 '14 #4

100+
P: 299
Can you post the code for your larger script and possibly your .NET app that is calling both scripts?
Feb 4 '14 #5

BHo15
100+
P: 135
Sure. Would you like it in this thread, or a new one?
Feb 4 '14 #6

100+
P: 299
Since we are still dealing with figuring out why it won't save, I think this one is fine for now. But when it comes to constructing a .NET app from the scripts, you will want to open a new one :)
Feb 4 '14 #7

BHo15
100+
P: 135
The vb.net code is a bit too much to post all of, but what I posted before is the button click event that should kick things off.

This is the code from the larger of the two vbscripts. It is a modification of a vbscript built by Brian Hartvigsen and Richard Crowley for OpenDNS stat pulling. Towards the end of the code, I open Excel, and a VBA macro that actually makes the pulled data a little cleaner. I know that VB.NET could also do that without using VBA, but maybe one thing at a time. :)

Expand|Select|Wrap|Line Numbers
  1. ' Modified from OpenDNS-Fetch for Windows  Brad Hodge <brad.h.hodge@gmail.com>
  2. '     Based on original fetchstats script from Richard Crowley
  3. '     Brian Hartvigsen <brian.hartvigsen@opendns.com>
  4.  
  5.  
  6. Dim CurPath
  7.     CurPath = Replace(WScriptFullName, WScript.ScriptName, "")
  8. Dim strK
  9.     strK="#$UnEqu1v0cal!?"
  10. Dim strNetwork
  11. Dim UserName
  12. Dim Password,strP
  13. Dim BDate,EDate, DateRange
  14. Dim objHTTP
  15.     Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  16. Dim URL
  17.     URL = "https://dashboard.opendns.com"
  18. Dim strEmail
  19. Dim objPassword
  20. Dim regEx
  21. Dim data
  22.  
  23. Set objHTTP = CreateObject("WinHttp.WinHttpRequest.5.1")
  24.  
  25. Function GetUrlData(strUrl, strMethod, strData)
  26.         objHTTP.Open strMethod, strUrl
  27.         If strMethod = "POST" Then
  28.                 objHTTP.setRequestHeader "Content-type", "application/x-www-form-urlencoded"
  29.         End If
  30.         objHTTP.Option(6) = False
  31.         objHTTP.Send(strData)
  32.  
  33.         If Err.Number <> 0 Then
  34.                 GetUrlData = "ERROR: " & Err.Description & vbCrLf & Err.Source & " (" & Err.Nmber & ")"
  35.         Else
  36.                 GetUrlData = objHTTP.ResponseText
  37.         End If
  38. End Function
  39.  
  40. URL="https://dashboard.opendns.com"
  41.  
  42.  
  43. Username = Wscript.Arguments.Item(0)
  44. If Len(Username) = 0 Then: Usage
  45. Network = Wscript.Arguments.Item(1)
  46. If Len(Network) = 0 Then: Usage
  47. BDate = Wscript.Arguments.Item(2)
  48. If Len(BDate) = 0 Then: Usage
  49. EDate = Wscript.Arguments.Item(3)
  50. If Len(EDate) = 0 Then: Usage
  51. strP = Wscript.Arguments.Item(4)
  52.  
  53. Set regDate = new RegExp
  54. regDate.Pattern = "^\d{4}-\d{2}-\d{2}$"
  55.  
  56. DateRange = BDate & "to" & EDate
  57.  
  58. ' Are they running Vista or 7?
  59. On Error Resume Next
  60. Set objPassword = CreateObject("ScriptPW.Password")
  61.     If strP="" Then strP= Inputbox("Please enter your password.")
  62.     Password = strP
  63.  
  64. Wscript.StdErr.Write vbCrLf
  65. On Error GoTo 0
  66.  
  67. Set regEx = New RegExp
  68. regEx.IgnoreCase = true
  69. regEx.Pattern = ".*name=""formtoken"" value=""([0-9a-f]*)"".*"
  70.  
  71. data = GetUrlData(URL & "/signin", "GET", "")
  72.  
  73. Set Matches = regEx.Execute(data)
  74. token = Matches(0).SubMatches(0)
  75.  
  76. data = GetUrlData(URL & "/signin", "POST", "formtoken=" & token & "&username=" & Escape(Username) & "&password=" & Escape(Password) & "&sign_in_submit=foo")
  77. If Len(data) <> 0 Then
  78.         Wscript.StdErr.Write "Login Failed. Check username and password" & vbCrLf
  79.         WScript.Quit 1
  80. End If
  81.  
  82. page=1
  83. Do While True
  84.         data = GetUrlData(URL & "/stats/" & Network & "/topdomains/" & DateRange & "/page" & page & ".csv", "GET", "")
  85.         If page = 1 Then
  86.                 If LenB(data) = 0 Then
  87.                         WScript.StdErr.Write "You can not access " & Network & vbCrLf
  88.                         WScript.Quit 2
  89.                 ElseIf InStr(data, "<!DOCTYPE") Then
  90.                  Wscript.StdErr.Write "Error retrieving data. Date range may be outside of available data."
  91.                  Wscript.Quit 2
  92.                 End If
  93.         Else
  94.                 ' First line is always header
  95.                 data=Mid(data,InStr(data,vbLf)+1)
  96.         End If
  97.         If LenB(Trim(data)) = 0 Then
  98.                 Exit Do
  99.         End If
  100.         Wscript.StdOut.Write data
  101.         page = page + 1
  102. Loop
  103. '--------------------------------------------------------
  104. 'DISPLAY AND FORMAT THE DATA IN EXCEL
  105. Dim xlApp, xlBook
  106. Dim CurrentPath
  107.     CurrentPath=Replace(WScript.ScriptFullName, WScript.ScriptName, "")
  108. Set xlApp = CreateObject("Excel.Application")
  109. Set xlBook = xlApp.Workbooks.Open(CurrentPath & "\StatTemplate.xltm")
  110. xlApp.Run "Process",(CurrentPath)
  111. '--------------------------------------------------------
  112.  
  113. Function Usage()
  114.     MsgBox("Username, OpenDNS Network, Begin Date, and Ending Date must be passed from 001-CallFetch.vbs")
  115. End Function
Feb 4 '14 #8

BHo15
100+
P: 135
One more bit of info... When I was using nothing but vbscript, I was using an INI file to store the user account info and preferences. When I built the GUI in vb.net, I started using My.Settings to store all of that information. Hence, when vb.net calls the vbscript, it is sending the account information from the vb.net settings. And when the VBA kicks off in Excel, it would eventually use the same to decide which categories to display.
Feb 4 '14 #9

100+
P: 299
I do have another question for you. Where are you specifying the values for the variables in your small script?
Feb 4 '14 #10

BHo15
100+
P: 135
My apologies. Here is the part I left off of the post regarding the vb app button click.

Expand|Select|Wrap|Line Numbers
  1.   Dim strUser As String = Me.txtUser.Text
  2.         Dim strNet As String = Me.txtNetwork.Text
  3.         Dim strBDate As String = Me.txtBDate.Text
  4.         Dim strEDate As String = Me.txtEDate.Text
  5.         Dim strPW As String = rc4(My.Settings.appPW, K)
The user obviously adds the info to the form, and then that info is stored into My.Settings.
Feb 4 '14 #11

100+
P: 299
The variables in your VB.NET application don't match those that you're trying to pass via cscript. If I was you, I'd do away with the small script all together and use something like this. You have to understand, this is merely an example but it worked fine for me. (Note: I used this code under a Button Click event within a VB.NET app)

Expand|Select|Wrap|Line Numbers
  1.         Dim strUser As String = Me.txtUser.Text
  2.         Dim strNet As String = Me.txtNetwork.Text
  3.         Dim strBDate As String = Me.txtBDate.Text
  4.         Dim strEDate As String = Me.txtEDate.Text
  5.         Dim strPW As String = rc4(My.Settings.appPW, K)
  6.  
  7.         'replace both instances of c:\directory with your own
  8.         Shell("cmd /c cscript //NoLogo c:\directory\TweakedFetch.vbs " & strUser & " " & strNet & " " & strBDate & " " & strEDate & " " & strPW & " > c:\directory\DNLD.csv")
  9.  
  10.  
Feb 4 '14 #12

BHo15
100+
P: 135
Thanks for the help. That worked fine (and was much more concise than mine :)), but it is still printing to cmd window.

Should I instead look to load the contents into a string, and then put that string into the DNLD.csv?
Feb 4 '14 #13

100+
P: 299
I'm still not able to replicate the code printing to the cmd window. Can you now include what you have setup under your Button Click event?
Feb 4 '14 #14

BHo15
100+
P: 135
Sorry this is being so problematic.

Here is the .net code under the button click.

Expand|Select|Wrap|Line Numbers
  1.     Private Sub btnFetchClick(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnFetch.Click
  2.         Dim strUser As String = Me.txtUser.Text
  3.         Dim strNet As String = Me.txtNetwork.Text
  4.         Dim strBDate As String = Me.txtBDate.Text
  5.         Dim strEDate As String = Me.txtEDate.Text
  6.         Dim strPW As String = rc4(My.Settings.appPW, K)
  7.         Dim CurrentPath As String = Application.StartupPath & "\"
  8.         Dim strFetch As String = ""
  9.  
  10.         Shell("cmd /c cscript //NoLogo " & CurrentPath & "TweakedFetch.vbs " & strUser & " " & strNet & " " & strBDate & " " & strEDate & " " & strPW & " >" & CurrentPath & "DNLD.csv")
  11.  
  12.     End Sub

And it is calling the vbscript that I sent on the last post. I am currently bypassing the shorter vbscript that I was using before, but just in case you are curious, this was it...

Expand|Select|Wrap|Line Numbers
  1. strEmail=wscript.arguments(0)
  2. strNetwork=wscript.arguments(1)
  3. BDate=wscript.arguments(2)
  4. EDate=wscript.arguments(3)
  5. strP=wscript.arguments(4)
  6.  
  7. Dim oShell
  8.  
  9. Set oShell = CreateObject("WScript.Shell")
  10.  
  11. If Not WScript.FullName = CurrentPath & "cscript.exe" Then
  12.     oShell.Run "cmd.exe /k" & WScript.Path & "\cscript.exe //NOLOGO " & Chr(34) & "C:\Users\bhodge\Dropbox\OpenDNS\Stats\TweakedFetch.vbs" & Chr(34) & " " & Chr(34) & strEmail _
  13.     & Chr(34) & " " & Chr(34) & strNetwork & Chr(34) & " " & Chr(34) & BDate & Chr(34) & " " & Chr(34) & EDate & Chr(34) & " " _
  14.     & Chr(34) & strP & Chr(34) & " >DNLD.csv"
  15.    WScript.Quit 0
  16. End If
Feb 5 '14 #15

100+
P: 299
This isn't normally the way I like to test things, but can you manually replace "CurrentPath" with a valid directory and/or run the following code to make sure the CurrentPath is what you expect it to be?
Expand|Select|Wrap|Line Numbers
  1. MsgBox(ApplicationStartupPath)
Just place that code in your button click event. It will show the absolute path that you are trying to write to.
Feb 5 '14 #16

BHo15
100+
P: 135
Good thought. The application.StartupPath is indeed what I was expecting. It is where the exe for the .net app lives, and where I have placed the vbscript and the Excel workbook.

I even tried yesterday hardcoding that path into the command, so that it said... "Shell("cmd /c cscript //NoLogo " & CurrentPath & "TweakedFetch.vbs " & strUser & " " & strNet & " " & strBDate & " " & strEDate & " " & strPW & " > C:\Users\bhodge\Documents\Visual Studio 2010\Projects\Call Fetch\Call Fetch\bin\Debug\DNLD.csv").

No luck. :(
Feb 5 '14 #17

100+
P: 299
K, sort of what I was expecting. Notice your path has spaces? CMD prompt isn't too 'space' savvy. You need to double quote a few things. Try this line.

Expand|Select|Wrap|Line Numbers
  1. Shell("cmd /c cscript //NoLogo """ & currentPath & """TweakedFetch.vbs " & strUser & " " & strNet & " " & strBDate & " " & strEDate & " " & strPW & " >""" & currentPath & """DNLD.csv")
Feb 5 '14 #18

BHo15
100+
P: 135
No go. :(

Should we call it quits on this one, and instead start a new thread with converting the main vbscript? It is taking the values that we send with the shell command, and interacting with the OpenDNS website.

I actually attempted a few days ago to convert it to .net, and got 99% of it "converted" without errors. I could post what I came up with for your review if you would like. But either way, we still have to eventually get the output from OpenDNS's website to a CSV.
Feb 5 '14 #19

100+
P: 299
I'm not a fan of "calling it quits", but I'm just not able to replicate the issue you're having. If you wish, open a new thread and post the VB.NET code you have and we'll iron it out :)
Feb 5 '14 #20

BHo15
100+
P: 135
I don't blame you one bit on the avoidance of quitting. I just don't want to be one of the "users" who just doesn't seem to get it. :)

I did go ahead and start a new thread (OpenDNS download automation - VBScript to VB.Net). Feel free to look at it now or later (your choice). I don't mind continuing on with this one at all.
Feb 5 '14 #21

Post your reply

Sign in to post your reply or Sign up for a free account.