• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 164
  • 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?


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

copy ..\MANIA.DAT MAN%TODAY%.DAT

copy ..\PMAN.DAT PIZZATRACK%TODAY%.DAT
c:
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
Range("A1").Select
    ActiveCell.FormulaR1C1 = "Date"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "Time"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "Type"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "Number"
   
    Range("A2").Select
    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")
   
    Application.Quit

0
crmaho01
Asked:
crmaho01
  • 2
1 Solution
 
SizeyCommented:
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.
0
 
crmaho01Author Commented:
Is there a way that I can email the files without the macros?
0
 
SizeyCommented:
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.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

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