converting an Excel macro into a VB .exe

[ukkz001]

Is it possible to convert a macro into a Visual Basic program?

 

[Damon Ostrander]

Hi ukkz001,

Yes, it is. The computer it runs on will still have to have Excel, since VB will have to use the Excel application via automation to access the Excel objects, such as worksheets and cells. In addition, it will undoubtedly require a bit of modification in order for the code to explicitly refer to the Excel objects--in particular the Application (Excel) object.

Damon

 

[erik.van.geit]

Damon,

Sounds intresting: can you point to an example or give a little sample ?

kind regards,
Erik

 

[ukkz001]

Yes, an example would be great or if you know of a web site that would have that inforamtion.


Thanks,
Kevin

 

[Damon Ostrander]

Hi Kevin and Erik,

If either of you would like to post an Excel macro that you would like to run from Visual Basic, I would be happy to post the converted code. Please, though, do not make it too long, say, not more than about 20 executable lines of code.

Damon

 

[tusharm]

Damon's on the money and what he is referring to are the same issues that would arise if you were to automate XL from another program (such as Word or PP). And, in reverse, the issues that would arise if you were to automate Word or PP from XL. Every reference to the other program must be fully qualified through a variable that represents the other program.

Of course, you also need the VB compiler (Visual Studio, basically) to compile the program.

A few examples:
Creation of Object Variables to Automate Another Office Application
http://msdn.microsoft.com/library/d...ariableToAutomateAnotherOfficeApplication.asp

Using the CreateObject and GetObject Functions
http://msdn.microsoft.com/library/d.../deconusingcreateobjectgetobjectfunctions.asp

And, a couple of articles I wrote:
How to safely instantiate another Office application and close it only if you started it
http://support.microsoft.com/default.aspx?scid=kb;en-us;555191
and
Program won't quit
http://www.tushar-mehta.com/excel/vba/xl_doesnt_quit/index.htm

 

[Justinlabenne]

Just to jump in with some other info:

I use this code as a front loader to open an Excel file that is within a folder named "components"

    Private Sub frmLoader_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Constant for the project's name
        Const sProjectName As String = "Project Name Here"


        'Constants for the Excel startup file name and components folder
        Const sStartupFile As String = "\StartupApp.xls"
        Const sComponentFolder As String = "\Components"


        'Late-binding for Excel application
        Dim objExcelApp As Object
        Dim sProjectPath As String


        On Error GoTo ErrHandle
        'Location for the project file startup path
        sProjectPath = Application.StartupPath & sComponentFolder

        'Create the excel instance
        objExcelApp = CreateObject("Excel.Application")


        'Open the startup file
        With objExcelApp
            .Visible = True
            .Workbooks.Open(sProjectPath & sStartupFile)
            .UserControl = True
        End With

ErrorExit:
        objExcelApp = Nothing
        Me.Close()
        Exit Sub

ErrHandle:
        Dim sMsg As String
        Dim sStyle As String
        Dim sTitle As String

        sMsg = "Application could not be started due to a missing component"
        sStyle = 0 + 16
        sTitle = sProjectName

        MsgBox(sMsg, sStyle, sTitle)
        GoTo ErrorExit
    End Sub

This is a late-binding example that I use to open an Excel file form a VB.Net form.
All it does is open the excel file when the compiled exe file is double clicked or opened (no enable or disable macros prompt either when the excel file is opened)
No other purpose except to have a custom icon and to make the appearance
appear un-excel like. You for sure would need a copy of Visual Studio, to be able to use the above code, I am working on examples of vb.net and vba (excel) interaction that I will be providing on my website sometime soon hopefully. I am attempting to make the above code into something people could download and use a fromt loaders for their own projects. It can be done, but without Visual Studio you would be stuck with some limitaitons. Working those out.

 

[erik.van.geit]

Damon:

If either of you would like to post an Excel macro that you would like to run from Visual Basic, I would be happy to post the converted code. Please, though, do not make it too long, say, not more than about 20 executable lines of code.

would this do?

Option Explicit

Dim a(2) As String

Sub test()
Dim title As String
Dim i As Integer

For i = 1 To 2
If i = 1 Then title = "FIRST NAME" Else title = "LAST NAME"
  Do
  a(i) = Trim(Application.InputBox("What's your " & LCase(title) & "?", title, , Type:=2))
    If a(i) = "" Then
    If MsgBox("Do you want to end the programm?", 36, "QUIT?") = vbYes Then Exit Sub
    End If
    If Len(a(i)) = 1 Then MsgBox "Please your ENTIRE " & LCase(title) & "!", 48, title
  Loop While a(i) = "" Or Len(a(i)) = 1
Next i
MsgBox "Hello, " & a(1) & " " & a(2) & "!", 48, "FULL NAME"
create_txt_file
End Sub

Private Sub create_txt_file()
Dim fs As Object
Dim txtFile As Object

Set fs = CreateObject("Scripting.FileSystemObject")
Set txtFile = fs.CreateTextFile("c:\windows\desktop\testfile.doc", True)
txtFile.WriteLine (a(1) & " " & a(2) & vbTab & Date)
'txtFile.Close
End Sub

kind regards,
Erik

 

[Justinlabenne]

Since Damon is quite possibly busy, this is how I did it:

Public Class frmForErik
    Inherits System.Windows.Forms.Form

#Region " Windows Form Designer generated code "

    Dim a(2) As String

    Private Sub btnCreate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnCreate.Click
        Dim title As String
        Dim i As Integer

        For i = 1 To 2
            If i = 1 Then title = "FIRST NAME" Else title = "LAST NAME"
            Do
                a(i) = Trim(InputBox("What's your " & LCase(title) & "?", title))
                If a(i) = "" Then
                    If MsgBox("Do you want to end the programm?", , "QUIT?") = vbYes Then Exit Sub
                End If
                If Len(a(i)) = 1 Then MsgBox("Please your ENTIRE " & LCase(title) & "!", , title)
            Loop While a(i) = "" Or Len(a(i)) = 1
        Next i
        MsgBox("Hello, " & a(1) & " " & a(2) & "!", , "FULL NAME")
        CreateFile()
    End Sub


    Sub CreateFile()
        Dim fs As IO.StreamWriter = IO.File.CreateText("c:\testfile.doc")

        Dim sFullName As String
        sFullName = a(1) & " " & a(2) & " " & Now

        With fs
            .WriteLine(sFullName)
            .Close()
        End With

        Dim sDocFile As String
        OpenFileDialog1.ShowDialog()

        sDocFile = OpenFileDialog1.FileName
        Dim sr As IO.StreamReader = IO.File.OpenText(sDocFile)

        With lstOutput
            .Items.Clear()

            Do While sr.Peek <> -1
                .Items.Add(sr.ReadLine)

            Loop
        End With
        sr.Close()
    End Sub

    Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
        IO.File.Delete("c:\testfile.doc")
        Me.Close()
    End Sub
End Class

As you can see the code is virtually the same as usign vba except where you are reading and writing to text files etc...

Erik, I adjusted the location of the text file output path to just a C:\ drive, just click create and the input boxes appear, then I threw in a file browser that should allow you to browse for the testfile.doc, the input is then read back into the listbox on the form. testfile.doc is then removed from the system when you close the form using the "Close" button.

Edited: (Dead Link)

it is compiled and should be able to run on your system. Because it's compiled you won't be able to see the code, but you can see the results

 

[erik.van.geit]

Justin,

Thank you for your efforts! When I try to open the file you offfered for download, I get the following message.
"can't find a file MSCOREE.DLL" So at the moment I can't open it. Do you have some advice ?

this is at the moment a bit over my hat, but experimenting and curiosity will be the engines to get further into this stuff.


Erik