Solved: Creating an EXE based on Excel VBA code

[rolly]

I'm not sure where to post this, but I just downloaded Visual Basic 2008 Express Edition and I'm wondering how I take the code from this:

http://www.vbaexpress.com/forum/showthread.php?t=27747

and build it into an EXE.

I'm not sure if it's possible, but if so any help is greatly appreciated, even a translation tutorial would be nice.

Note: the above linked code works great in Visual Basic 6.3

Thanks,

Rolly

[Tommy]

It will not work as posted, as with any upgrade there will be issues that need to be addressed. I don't have vb 2008 here otherwise I would translate it for you. I have it at home and will not be there to do anything until tomorrow. So unless someone else can help you out I will be back tomorrow to post the code.

[rolly]

Awesome, Thank your for your help.

Hopefully I can study the differences and learn some too.

This may be the wrong place to ask, but what books do you recommend for beginning users of Visual Basic 2008 Express Edition?

Rolly

[Tommy]

To be honest I watched the videos. Searched the internet, found examples ......

[Tommy]

In a form with a OpenFileDialog control and a button.
the OpenFileDialog has a filter it is set to Text Files|*.txt
the default ext is txt
[VBA]Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim mFleNm As String = "", mData As String = "", mFileName As String = ""
If OpenFileDialog1.ShowDialog() Then
mFleNm = OpenFileDialog1.FileName
mData = My.Computer.FileSystem.ReadAllText(mFleNm)
mFileName = mFleNm.Substring(InStrRev(mFleNm, "\"))
mFileName = mFileName.Substring(0, Len(mFileName) - 4)
mData = Replace(mData, "0test", mFileName)
mData = mData.Substring(0, Len(mData) - 2)
mData = mData & vbCrLf & vbCrLf & "Z:\0test\0test.EVT)"
My.Computer.FileSystem.DeleteFile(mFleNm)
My.Computer.FileSystem.WriteAllText(mFleNm, mData, False)
Else
MsgBox("No File selected.")
End If
End Sub[/VBA]

[Tommy]

Found an error
[VBA]If OpenFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then[/VBA]

[rolly]

Thanks for your help,

I started with a new template for a "Windows Form Application" I added an "OpenFileDialog" and a button but the "OpenFileDialog" wouldn't show up in the form1 box (the button1 did show up)

I added the code, and the correction to fix the error and built it but nothing happened, no errors showed up. The mouse goes to an hour glass for about half a second and them goes back to a pointer. I think VB 2008 EE is doing something, but then no open file dialog pops up, and it ascts like the build is complete. I think I'm missing something but I'm not sure where to look first to try to correct it.

[Tommy]

The OpeFileDialog will not show up on the form. You have to call it before it will show up. It should be below the form, you can then select it and look at the properties, these properties need to be set as per the the post #5. If this does not work I will post the project tonight.

Meanwhile press the F8 key and try stepping through the project and see if something doesn't work or looks fishy.

[rolly]

Great, I got it to work, in testing it I found it only works on the machines when encoded in ANSI, after searching for a little bit, I changed this line:
[vba]
My.Computer.FileSystem.WriteAllText(mFleNm, mData, False)
[/vba]
to this:
[vba]
My.Computer.FileSystem.WriteAllText(mFleNm, mData, False, System.Text.Encoding.Default)
[/vba]
and it fixed the problem.

Thanks you for your help.

One last thing i'd like it to do is to add some hyphens to the filenames. I'd like to open the file:
11223344.evt
and have all the above code ran and have the filename changed to:
11-2233-44.evt

Thanks for your help, sorry I didn't get back sooner, some other projects and meetings distracted me.

Rolly

[Tommy]

I was going to say I didn't consider encoding. This would only come into play when there are 3rd party apps looking into the text file.

So far as the file name change, what is the logic? first 2 characters and a hyphen, next 4 then hyphen?

[rolly]

So far as the file name change, what is the logic? first 2 characters and a hyphen, next 4 then hyphen?

yes

Thank you for your continuing help, I have a lot to learn but I'm starting to pick it up a little faster.

Rolly

[Tommy]

I have not tested this because I don't have VB express here. This should work though but I may be off a little, well I am but I am talking about the code now LOL.

Also I have not included anything for multi selct - in other words allowing the user to selecet more than 1 file at a time.
[VBA]
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim mFleNm As String = "", mData As String = "", mFileName As String = ""
If OpenFileDialog1.ShowDialog() Then
mFleNm = OpenFileDialog1.FileName
mData = My.Computer.FileSystem.ReadAllText(mFleNm)
mFileName = mFleNm.Substring(InStrRev(mFleNm, "\"))
mFileName = mFileName.Substring(0, Len(mFileName) - 4)
mData = Replace(mData, "0test", mFileName)
mData = mData.Substring(0, Len(mData) - 2)
mData = mData & vbCrLf & vbCrLf & "Z:\0test\0test.EVT)"
'I am deleting the old file here
My.Computer.FileSystem.DeleteFile (mFleNm)
mFleNm = mData.Substring(0, 2) & "-" & mData.Substring(3, 4) & "-" & mData.Substring(7)
My.Computer.FileSystem.WriteAllText(mFleNm, mData, False)
Else
MsgBox ("No File selected.")
End If
End Sub

[/VBA]

[rolly]

I get this error "ArgumentException was unhandled"
"Illegal Characters in Path"

I think it's close though.

[Tommy]

Wrong Variable, I am guessing at this point. I will test tonight and see what is going wrong if this one doesn't get it.
[VBA]
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim mFleNm As String = "", mData As String = "", mFileName As String = ""
Dim mNewFleNM as String = ""
If OpenFileDialog1.ShowDialog() Then
mFleNm = OpenFileDialog1.FileName
mData = My.Computer.FileSystem.ReadAllText(mFleNm)
mFileName = mFleNm.Substring(InStrRev(mFleNm, "\"))
mFileName = mFileName.Substring(0, Len(mFileName) - 4)
mData = Replace(mData, "0test", mFileName)
mData = mData.Substring(0, Len(mData) - 2)
mData = mData & vbCrLf & vbCrLf & "Z:\0test\0test.EVT)"
'I am deleting the old file here
My.Computer.FileSystem.DeleteFile (mFleNm)
mNewFleNM = mFileName.Substring(0, 2) & "-" & mFileName.Substring(3, 4) & "-" & mFileName.Substring(7)
mFleNm = Replace(mFleNm, mFileName, mNewFleNM)
My.Computer.FileSystem.WriteAllText(mFleNm, mData, False)
Else
MsgBox ("No File selected.")
End If
End Sub
[/VBA]

[rolly]

Awesome,
Great guess, the filenames were coming out short (i.e. 11-2233-4.evt)
so I changed this line
[vba]
mNewFleNM = mFileName.Substring(0, 2) & "-" & mFileName.Substring(3, 4) & "-" & mFileName.Substring(7)
[/vba]

changing the last number to 6 fixed it, now it's:
[vba]
mNewFleNM = mFileName.Substring(0, 2) & "-" & mFileName.Substring(3, 4) & "-" & mFileName.Substring(6)
[/vba]

and the filenames are now 11-2233-44.evt

Thanks for your help, I may be back in the future to ask about migrating some M$ Word VBA to VB Express.

Thanks again,

Rolly