Change VBA to VB executable

Posted on 2005-04-22
Last Modified: 2010-05-02
I currently have a bat file to copy some DAT files and then open excel to format the DAT files, save them as .xls, and then email them.  This will work with no problems on a client machine that has office.  I am now needing to move the process to a server 2000 machine.  Any ideas?

cd Mania
set DATE=
set TODAY=%DATE:~4,2%%DATE:~7,2%%DATE:~10,4%


cd "C:\Program Files\Microsoft Office\OFFICE11"
excel "C:\Documents and Settings\cxm\My Documents\Automation Process\Auto1.xls"
excel "C:\Documents and Settings\cxm\My Documents\Automation Process\Auto2.xls"

  MY VBA Code from the excel files
    ActiveCell.FormulaR1C1 = "Date"
    ActiveCell.FormulaR1C1 = "Time"
    ActiveCell.FormulaR1C1 = "Type"
    ActiveCell.FormulaR1C1 = "Number"
    Application.CutCopyMode = False
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;Z:\Mania\MAN" & Format(Now(), "mmddyyyy") & ".DAT" _
        , Destination:=Range("A2"))
        .Name = "MAN" & Format(Now(), "mmddyyyy")
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = False
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(5, 2, 2, 2, 1)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    ActiveWorkbook.SaveAs ("C:/MAN" & Format(Now(), "mmddyyyy"))
    ActiveWorkbook.SendMail "", "MAN" & Format(Now(), "mmddyyyy")

Question by:crmaho01
    LVL 2

    Accepted Solution

    You aren't going to like this much, but because of the issues below the best solution (in terms of cost and time) is to install Office on the server.

    In order to create an executable file you need to use a "proper" version of VB (such as VB6 Professional/Enterprise or VB.Net Professional), rather than the VBA editor that is provided in Office applications.   Even if you manage to find a cheap second-hand version, the cost is likely to be similar to the basic version of Office.

    The next problem is that you are using several functions which are specific to Excel, for which need to have Excel installed.  It is possible to treat an .Xls file as a database from a VB6/VB.Net program, but you cannot change any of the formatting, only the text.

    Automatic emailing is also an issue, as you cannot automate Outlook Express, and Outlook isn't too easy since big security updates came in a few years ago.  The closest you could manage is to get the email created with a mailto: , but then it wouldn't get sent without somebody manually pressing the Send button.

    Author Comment

    Is there a way that I can email the files without the macros?
    LVL 2

    Expert Comment

    It is possible, but I have never done it myself (the closest I've done is create an email without sending)  - from what I have seen it takes far too much work.  It would be easiest to use the functionality of Office if it meets your needs.

    Unless you've got a proper version VB, I wouldn't even consider a route other than installing Office.

    If you have got VB, it will be a lot of work to get the data formatted suitably too.

    Featured Post

    Gigs: Get Your Project Delivered by an Expert

    Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

    Join & Write a Comment

    There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
    Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
    As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    745 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    19 Experts available now in Live!

    Get 1:1 Help Now