VBA to VB

  • Thread starter Thread starter Striker
  • Start date Start date
S

Striker

I have never used VB, so sorry for the novice questions. I have a very
small app in vba that only uses excel as a place to put a button and some
code that I need to move to VB. Now it seems soemone who uses it does not
have access to excel.

In the following code the application causes some problems in VB. and the
cursor xlwait also causes some problems. I wonder if someone can help me
convert this to a actual VB application. Also maybe a silly question, but I
have been told that if I compile this on a Windows XP machine, it may only
work on an XP machine and not a windows 2000 machine. Is this true, and if
so is there anything I can do to make it work on both operating systems?

Thank You

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Open_Write_File()
Dim sLineOfText As String
Dim sFile1 As String
Dim sFile2 As String
Dim iInt As Integer

'set cursor to let user know I'm working.
'Application.Cursor = xlWait


'MsgBox "First lets pick the file you want to remove phone numbers from",
vbOKOnly
sFile1 = Application.GetOpenFilename("Text Files (*.txt), *.txt",
Title:="PICK THE FILE TO REMOVE PHONE NUMBERS FROM")
MsgBox "OK, now lets pick a file name you want to save the new fils as",
vbOKOnly

sFile2 = Application.GetSaveAsFilename("Text Files (*.txt), *.txt",
Title:="CREATE NEW FILE WITHOUT TELEPHONE NUMBERS")


Open sFile1 For Input As 1
Open sFile2 For Output As 2

iInt = 0
Do Until EOF(1)

Line Input #1, sLineOfText
sLineOfText = Trim(Mid(sLineOfText, 1, 729))
Print #2, sLineOfText
iInt = iInt + 1
Loop
Close #1, #2

'return cursor to normal
Application.Cursor = xlDefault

MsgBox iInt & " records updated without phone numbers", vbOKOnly

End Sub
 
A couple of things:

This is a newsgroup that discusses the .NET development environment.
Neither Excel VBA or VB 6.0 are part of that.

When you write your VB 6.0 code, you must make a reference to the Excel
Object Library (from the Project menu, choose References), before you can
use any object that is part of Excel's "world".

After you have done that, you'll need to make an "instance" of the Excel
Application object:

dim xl As New Excel.Application

You then go through your variable to do other Excel related tasks.

There is much more to know, but again, this is not the place.

I would suggest you try: Microsoft.Public.Excel
 
Seems to be some confusion since I accidentally posted this in the VB
section and was told to go to Dot Net. I am trying to move a VBA app from
VBA to DOT Net as one person does not have access to VBA.

I hope this clears up things.
 
You need to make it clear: Do you want to "VBA to VB" or "VBA to VB.NET".
Everyone (maybe except for those in MS, since MS officially ended support to
VB, so for them, there is only one thing called VB, that is, VB.NET) regard
VB and VB.NET as totally different things.

If you want to do VBA to VB, you need to post to one of the NGs dedicated to
VB (or call it classical VB).

If you want to use VB.NET ( or mor egeneral, .NET, since in .NET world,
which language is not important, you can choose C#, VB.NET, C++ to do the
same thing in the very similar way) to replace your VBA code, you have
coices of building a stand-alone app to automation your Excel, building a
COM interop dll that you can use inside Excel VBA, or using VSTO to make
your code have better integration with Excel...

With VB (classical VB) you have similar choices and you may find it is a bit
easier to transfer your code from VBA to VB, after all, they are the
technologyies of the same generation.

Without knowning what exactly you are intending to do and see more of you
code, and even you are trying on VB or VB.NET, it is difficult to say
something really helpful.
 
Doesn't have access to VBA? You mean that person doesn't have MS Office?
If you have MS Office, you have access to VBA.

Anyway, going from VBA to VB.NET is not a simple switch and it's not
something to jump into lightly. Anyone who who want to be able to run the
applicaiton will need the .NET Framework (the same version as you create the
app. with). And, there is the small matter of learning what .NET is and how
to go about writing the application.

Since you want to work with Excel, you'll need to use something called COM
InterOp from within .NET, which right away, diminishes some of the
performance benefits of a .NET applicaiton.

If you are interested in .NET simply because one person doesn't have VBA
capabilities and not necessarially because of all that .NET has to offer,
I'd say you are making a huge mistake.

I wouldn't go out and buy a Leer Jet without knowing how to fly an airplane
first.
 
Hello Striker,

Your app below appears to be an excellent candidate for moving to VB.Net and
I applaud your efforts. I included the VB.Net newsgroup in the list so that
some of the extremely helpful folks who haunt that list can provide some
more help.

In VB.Net, we don't have cursor.xlwait. That is an Excel notion. In
VB.Net, you get the object that represents the form itself and you set the
Cursor.Current property for the form to one of the predefined cursors (like
Cursors.WaitCursor ) but don't forget to set it back to Cursors.Default.
You can find more info here:
http://msdn.microsoft.com/library/d...SystemWindowsFormsCursorClassCurrentTopic.asp

If you compile it on any machine that has Visual Studio.Net installed, it
will run on any machine with the .Net framework. Note, however, that there
are three versions of the framework (1.0, 1.1, and 2.0) and three versions
of VS.Net that coorespond to them. The version of VS.Net will dictate which
version of the framework needs to be installed on your target machine.

That said, you can install the .Net framework on your customer's Windows
2000 machine as long as it has Service Pack 2 installed. There are a couple
of links on the Microsoft site for downloading the specific version of the
framework that you need.
Framework 1.0:
http://www.microsoft.com/downloads/...ee-a83f-4e21-b05a-009d06457787&displaylang=en
Framework 1.1:
http://www.microsoft.com/downloads/...E3-F589-4842-8157-034D1E7CF3A3&displaylang=en
Framework 2.0:
http://www.microsoft.com/downloads/details.aspx?FamilyID=0856eacb-4362-4b0d-8edd-aab15c5e04f5

I hope this helps. I'm sure that the good folks in the VB newsgroup can
help you further if you have follow-up questions. Welcome to .Net.

Striker said:
I have never used VB, so sorry for the novice questions. I have a very
small app in vba that only uses excel as a place to put a button and some
code that I need to move to VB. Now it seems soemone who uses it does not
have access to excel.

In the following code the application causes some problems in VB. and the
cursor xlwait also causes some problems. I wonder if someone can help me
convert this to a actual VB application. Also maybe a silly question, but
I
have been told that if I compile this on a Windows XP machine, it may only
work on an XP machine and not a windows 2000 machine. Is this true, and
if
so is there anything I can do to make it work on both operating systems?

Thank You

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sub Open_Write_File()
Dim sLineOfText As String
Dim sFile1 As String
Dim sFile2 As String
Dim iInt As Integer

'set cursor to let user know I'm working.
'Application.Cursor = xlWait


'MsgBox "First lets pick the file you want to remove phone numbers from",
vbOKOnly
sFile1 = Application.GetOpenFilename("Text Files (*.txt), *.txt",
Title:="PICK THE FILE TO REMOVE PHONE NUMBERS FROM")
MsgBox "OK, now lets pick a file name you want to save the new fils as",
vbOKOnly

sFile2 = Application.GetSaveAsFilename("Text Files (*.txt), *.txt",
Title:="CREATE NEW FILE WITHOUT TELEPHONE NUMBERS")


Open sFile1 For Input As 1
Open sFile2 For Output As 2

iInt = 0
Do Until EOF(1)

Line Input #1, sLineOfText
sLineOfText = Trim(Mid(sLineOfText, 1, 729))
Print #2, sLineOfText
iInt = iInt + 1
Loop
Close #1, #2

'return cursor to normal
Application.Cursor = xlDefault

MsgBox iInt & " records updated without phone numbers", vbOKOnly

End Sub
Click to expand...

--
--- Nick Malik [Microsoft]
MCSD, CFPS, Certified Scrummaster
http://blogs.msdn.com/nickmalik

Disclaimer: Opinions expressed in this forum are my own, and not
representative of my employer.
I do not answer questions on behalf of my employer. I'm just a
programmer helping programmers.
--
 
hi,
For cursors I prefer in general to avoid setting the current form
cursor.

While there can be exceptions, in general I think it's safer
and less error prone to use:

Windows.Forms.Cursor.Current = Cursors.WaitCursor

if you need to use application.doevents() (for instance in a loop)
use something like:

Application.DoEvents()
If Not Windows.Forms.Cursor.Current = Cursors.WaitCursor Then
Windows.Forms.Cursor.Current = Cursors.WaitCursor

where doevents always precede, because otherwise the cursor
automatically begins to listen form events.

-tommaso

Nick Malik [Microsoft] ha scritto:
 
I disagree. If the only reason for using .NET is that 1 user doesn't have
VBA, I would first ask what could be done to get that 1 person MS Office.

What if that 1 person doesn't have the .NET Framework (or doesn't have the
right version of the Framework)? Now, we are back to sqaure one.

The learning curve to be undertaken isn't worth it for 1 person who doesn't
have Excel.

My 2 cents....

-Scott
 
I agree.

Make it a requirement that the person has excel in order to run your
application. I would rather have something in VBA ... VB ... that works and
that you can maintain ... than to open a whole other issue regarding
'VB.Net' deployment and support.

I betcha that it would be cheaper and easier for you to simply buy that
'other person' a licence copy of Excel than it would be for you to re-write,
build, deploy and maintain your application in VB.Net ... or another dotNET
language...now if person #2 comes along and wants to use your application
.... it may no longer be feasible for you to buy them Excel.

If this is an internal / company application, make it a requirement that
other co-workers have excel to operate it.

If this is a shareware / freeware product ... make / state a requirement
user needs excel to run it...

If this is a 'for sale / puchase - put food on your table application' ...
you need to ask yourself, will any additional 'costs' associated with
re-doploying it in VB.Net be off-set by the numebr of new licences / users
that do not have 'Excel'...if not, market / sell it as an 'excel' add-on or
utility. ... if you will lose a significant number of 'potential sells', you
should porting the application.

These are extreme.... but my underlying message is ... as previous post ...

Do not move it to the latest and greatest technology for the exception ...
ie 1 user ... wait til either ... market demand requires you or Company
Policy excludes it.

'Cause, it will be a long time before MS will be able to phase out VBA from
Excel...Word...Access...unless I am blind and do not know something that is
public knowledge...re Word / Excel / Access VBA support or moving to VBA.Net
???

JEff



Scott M. said:
I disagree. If the only reason for using .NET is that 1 user doesn't have
VBA, I would first ask what could be done to get that 1 person MS Office.

What if that 1 person doesn't have the .NET Framework (or doesn't have the
right version of the Framework)? Now, we are back to sqaure one.

The learning curve to be undertaken isn't worth it for 1 person who
doesn't have Excel.

My 2 cents....

-Scott
Click to expand...
 
Even if you decide to port your application over to VB.Net I believe that you
still need Excel to perform COM Interop with, in other words, even if you do
write this killer app in VB.Net, you still have to have Excel installed on
the client machine. IMHO.

jeff said:
I agree.

Make it a requirement that the person has excel in order to run your
application. I would rather have something in VBA ... VB ... that works and
that you can maintain ... than to open a whole other issue regarding
'VB.Net' deployment and support.

I betcha that it would be cheaper and easier for you to simply buy that
'other person' a licence copy of Excel than it would be for you to re-write,
build, deploy and maintain your application in VB.Net ... or another dotNET
language...now if person #2 comes along and wants to use your application
.... it may no longer be feasible for you to buy them Excel.

If this is an internal / company application, make it a requirement that
other co-workers have excel to operate it.

If this is a shareware / freeware product ... make / state a requirement
user needs excel to run it...

If this is a 'for sale / puchase - put food on your table application' ...
you need to ask yourself, will any additional 'costs' associated with
re-doploying it in VB.Net be off-set by the numebr of new licences / users
that do not have 'Excel'...if not, market / sell it as an 'excel' add-on or
utility. ... if you will lose a significant number of 'potential sells', you
should porting the application.

These are extreme.... but my underlying message is ... as previous post ...

Do not move it to the latest and greatest technology for the exception ...
ie 1 user ... wait til either ... market demand requires you or Company
Policy excludes it.

'Cause, it will be a long time before MS will be able to phase out VBA from
Excel...Word...Access...unless I am blind and do not know something that is
public knowledge...re Word / Excel / Access VBA support or moving to VBA.Net
???

JEff
Click to expand...
 
I don't know why I would need excel, that is the whole idea of porting to
VB.NET, so excel is not needed. If you look at the short code all it is
doing is opening a text file and copying the first 729 bytes of each line
into another text file. The only reason it uses excel is for a place to put
a button to run the code (Macro).
 
I agree ... you do not need excel if this is all you are doing. I had
assumed that since you wrote an 'excel' marco, that you were extending the
functionality of excel (custom data manipulation / imports / ...) ...
however, this is not the case ... as you have pointed out ... then, yes I
agree with Nick's response, VBA / Excel should not be you choice of
development platform.

If all you are doing is coping a few lines from one text file to another ...
VB.Net can easily accomplish this ... however - depending on the development
version of VS ... you will require your end-users to have the appropriate
..net framework installed. If you have access to VB6 IDE ... this is easier
to build and deploy ... and would be independant of excel.

Jeff.
 
Hi

Having looked at the replies (then re-reading your original post) I'd
say that what you're doing is very straight forward and could easily be
ported to either VB6 or VB.Net, the choice of which platform you go to
(if you go through either) is up to you. For such a small app you may
find that the user having to install the .Net framework (if they don't
already have it) etc to run this may feel like a bit of overkill
(unless they're likely to need to the framework for other things in the
near future, or if it's already installed) - in that instance I'd go
for VB6 (if you've got access to it) The code should port to VB6 very
easily (i.e. just remove the Excel specific stuff (the get/set
filenames and the set cursor seem to be the only bits from first
glance) and replace with VB6 functionality as required.

If it's .Net you decide to go down, you should find the port to that
very straight forward as well, so I suppose it depends on the tools you
have available and the longevity of the actual app (if it's going to be
around for a while then is it worth getting .Net framework installed
anyway as other things will no doubt be using it).

Either way I don't envisage you having much trouble porting, whichever
way you go.

Cheers
Martin
 
Back
Top