• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 166
  • Last Modified:

Change VBA to VB executable

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 "email.address@company.com", "MAN" & Format(Now(), "mmddyyyy")

  • 2
1 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.
crmaho01Author Commented:
Is there a way that I can email the files without the macros?
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now