VBA code in Excel linked to HMI

A

Thread Starter

Andrew Ng

I am trying to write a VBA code in excel for a what I feel is a rather silly task! and don't ask me why we have to do it. OK here goes:-I have an opened spreadsheet in .xls format which is linked via DDE to In-Touch. I want to rename it and save it into a csv format so that the live DDE data becomes captured (dead). All these have to be done automatically at a specified time with no operator input.

I got through most of the code and everything works fine until Excel pops up with the question of "do you want to save changes to the file C:\ etc..." It gives you an option of Yes, No and Cancel. I have tried ways and means to send a keystroke to acknowledge it. It seems that it does not accept the sendkeys command in VBA. You still have to manually answer excel which somehow defeats the purpose of this whole exercise. Is there any way of disabling these warning functions in excel or does anybody have a better way of doing it. Your worthy advise is much appreciated.......
 
H

Heath Lovell

I think there is a way to do it. It has been a while since I have worked alot in VBA, but I think there is a way to turn the warnings off so that is saves without the pop-up box. Sorry I could not help more.
 
C

Calta Computer Systems Limited

The simplest way of getting around this is to define an auto_close subroutine and include in it one statement

application.save

Syd. Deitch

Calta Computer Systems Limited
Suite 230, 550 - 71st Ave. S.E.
Calgary, Alberta, Canada T2H 0S6
Phone - 403-252-5094 Fax - 403-252-5102
e-mail - [email protected]
Web - http://www.calta.com
 
J

JR Musselman

In real VB, this is easy - I do it all the time. Here's the syntax: (the last line is the key line for you; it will turn off ALL message alerts).

'Start the excel COM and make it visible.
Set objexcel = GetObject("", "excel.application")

'Start a workbook.
Set objworkbook = objexcel.Workbooks.Add

'Turn off the alerts, otherwise user will have to confirm my actions.
objexcel.DisplayAlerts = False
 
H

Hellstern, Manny

Andrew:

I came across this "TIP" a while back. It is written from the perspective of a VB app using MSOffice as opposed to using VBA from within MSOffice. If it is not directly applicable, it may give you some ideas.

Here's the content of the "TIP":

Programmatically turn off warning messages in MS Office applications

When using Office Automation in your Visual Basic application, you'll often want to turn off MS Office prompts. For instance, when deleting an Excel worksheet via code, you probably don't want
Excel to ask the user if they really want to delete the worksheet. Or in Access, when you run an Action query, chances are you don't want Access to ask the user's permission before deleting records.

You're probably aware that in Microsoft Access, to turn off such warnings, you use the

DoCmd.SetWarnings = False

statement. This command turns off internal program warnings. The DoCmd object, however, is a throwback to the days when all the Office applications used different internal programming languages. As a result, it doesn't work in any of the other Office applications.

To achieve the same behavior in the other Office applications, use the application's DisplayAlerts property. As you can guess, this property determines how a particular Office application shows its alert messages. However, while each Office application other than Access uses this property, they implement it in slightly different
ways.

For example, in Excel this property accepts a simple Boolean value that turns the display messages on and off. With this in mind, to delete the active worksheet via code, you'd use

Public Sub DeleteWorksheet()
Application.DisplayAlerts = False
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True
End Sub

Notice that we set the alerts back on after we turned them off. That's because Excel doesn't do so automatically when it completes the code execution. This behavior is consistent throughout Office.

Word uses a slightly different version of the same property. Instead of accepting a Boolean value, Word uses three Long constants: wdAlertsNone, wdAlertsAll and wdAlertsMessageBox. The first two settings are fairly self-explanatory. The last setting tells Word to display only standard message box alerts.

Under normal conditions, the code statement:

ActiveDocument.Close

would generate a message box asking if you wanted to save the document in question. Using the DisplayAlerts property, the following code

Application.DisplayAlerts = wdAlertsNone
ActiveDocument.Close
Application.DisplayAlerts = wdAlertsAll

would immediately open the SaveAs dialog box without the initial prompt.

Hope this is helpful.

Manny Hellstern
Mustang Engineering
Houston, TX
 
J
Andrew,

You can use the WWExecute script in InTouch to run a macro in Excel that will close the file without saving changes.

Below is the script for InTouch:

{THIS SCRIPT CALLS THE "CLOSEEXCEL" MACRO THAT IS LOCATED IN THE REPORT.XLS FILE}

{Define the macro name}
Macro="CloseExcel";

{Prepare the macro command to be sent to Excel}
Command="[Run(" + StringChar(34) + Macro + StringChar(34) + ",0)]";

{Activate Excel so that it has focus before the macro is run}
ActivateApp "Microsoft Excel - Report.xls";

{Execute the macro}
WWExecute("excel","system",Command);




Here is the Macro code that is written in Excel and executed by InTouch:

Sub CloseExcel()
'
' CloseExcel Macro
'
'*************************************
'* EXIT EXCEL WITHOUT SAVING CHANGES *
'*************************************
'
'Macro recorded 3/26/2001 by Jim Majsak

'Quit Excel command
Application.Quit

'Send a "No" response to the "Save Changes?" dialog box
Excel.SendKeys "N"

End Sub
 
H

Hart, Brett, CA NAR/US

Andrew,

In your VBA code all warnings can be disabled with the following statement:

Application.DisplayAlerts = False

Excel will simply choose the default response rather than prompting. Make sure you set the value back to True in your macro as this shuts off alerts for the entire Excel session.

Brett Hart
[email protected]
 
D

David Bergeron, P.E.

The close method of the workbook object has a parameter to save or supress save changes before closing. This parameter is optional but if
you leave it out, you will get the dialog box asking if you want to save changes. Here is an example of its use from the Excel Help.

Close Method Example

This example closes Book1.xls and discards any changes that have been made to it.

Workbooks("BOOK1.XLS").Close SaveChanges:=3DFalse

David Bergeron, P.E.
Thompson Equipment Co.
 
P
<<I got through most of the code and everything works fine until Excel pops up with the question of "do you want to save changes to the file C:\ etc..." It gives you an option of Yes, No and Cancel.>>

Try using this command...it tells Excel the file has already been saved.

ThisWorkbook.Saved = True

Hope this helps.

Pat Russell
 
I just finished a little project that sounds similar to the one you are trying but I decided after messing around with excel and dde links etc., to go after the problem from the other direction. In Intouch (I am using version 5.6b) there is a set of File commands you can use to write/read/create .csv files. Im my case I had to store 5 values on an event driven basis. So I wrote a small condition script triggered with a discrete input which moves my tag values to five sequntial tags. Next I used the FileWriteFields( , , , ,) command to write sequentially named tags to a csv file.

This function returns an integer value indicating the next place in the file to write the next record. This is all easier than I am making it sound but if you read the help file in intouch for system functions it will make sense. By the way I also wrote a little script to create a date-time file name so I start a new file at 12am and at 12 pm every day. If you would like to see how I did it drop me an email [email protected]
 
M

Martin Medak

I have implemented this exact thing. The purpose was to generate a daily report saved to a CSV file and also print the report.
A third party program called MacroScheduler was utilized to initiate a script file at a predeterminde time, that in turn activated a Macro in Excel with some VBA code the job was done. By the way MacroScheduler was about $40 and saved much more than that in programming time.

Contact me at [email protected] if you need more detail.
 
Can I have your code? I have data that is live and I can copy it as dead to the next row but the live data won't refresh while the macro is running. Do you know how I can get the dde to continue to send data while I am running my macro?

I don't think I can use any DDE commands because the data is coming from a proprietary application that is not subject to VBA scripting that I know.
 
R

Ranjan Acharya

You can just use the statement "ThisWorkbook.Saved = True" in your shutdown macro. Provided that nothing happens between this statement and the actual save / exit, you are OK.

As far as the other gentleman's question about DDE and suspension during macros, he could insert a "DoEvents" command into his macro to allow other
tasks to work.

For true DDE, the DDEML is the best way to go for VB or VBA.

Ranjan
 
Just a word of caution,

I wrote a ' Excel.SendKeys "Y" ' line to get rid of "Would you like to save changes..." and then I stopped...

First; If you work internationally, the "Y" is not always gonna be "Y", and secondly, should I send "Y" on "Would you like to format your C:\ Drive?" prompt?

So; If you need a "Y" do the following:

'Supress prompt
Application.DisplayAlerts = False

'Do your save or close or both here
ActiveWorkbook.SaveAs strPath
ActiveWorkbook.Close

'Allow prompt
Application.DisplayAlerts = True


Happy programming.
 
Top