Sign in to follow this  
Followers 0
Patrickc

Opening an existing excel sheet from FT view SE

9 posts in this topic

I would like to open an existing excel sheet from FT View SE. I know it could be possible using Macro commands, but I have no idea how to code in Visual Basic. Could anyone help me out and maybe provide the code necessary for me to just paste in the macro so I can just run it? Thank you Patrick

Share this post


Link to post
Share on other sites
VBA code resides at the display level. Drop a new command button (the gray one that can be scripted) onto a screen, then right-click and select VBA code. You'll be directed to that button's code automatically, which is currently empty. It will look like this: Private Sub Button1_Released() End Sub Before coding, you need to reference the MSExcel object model: Tools -> References Mcirosoft Excel ## Object Library (## dependent upon version) Now replace the existing code for the button with this: Private Sub Button1_Released() Excel.Application.Workbooks.Open "c:\excel.xlsx" Excel.Application.Visible = True End Sub This will open and show MSExcel with the file open. Note a few things: The excel thread will stay open until the user closes the window (you can see it as EXCEL.EXE in task manager.) I cannot remember if this will keep the VBA thread active as well, as I usually don't expose the excel GUI to the operator. If you're planning on doing any kind of programmatic manipulation, I recommend instancing the application and workbook objects, then destroying upon completion. Finally, note that the VBA code in ViewSE runs client-side, not server-side. This means that the Excel file will need to be on each client; it can't just exist at the server level unless you're mapping a drive.

Share this post


Link to post
Share on other sites
Thankyou for your reply. I seem to have a problem finding the Tools -> References menu in Factorytalk View I added the code to the button successfully, but it didnt work since I didn't reference the MSExcel object model. Thanks Patrick EDIT: Forget what I just said above - I managed :) Realised had to choose tools from VBA not FT view Thank you for your help, Patrick Edited by Patrickc

Share this post


Link to post
Share on other sites
Could I maybe ask you another question? How could I create a macro in excel so that the spreadsheet updates every 45 seconds when opened Thank you Patrick

Share this post


Link to post
Share on other sites
What's in there that you're looking to update? I'm assuming you mean that something is programmatically pushing data into the sheet?

Share this post


Link to post
Share on other sites
I have and excel sheet with the purpose to create a schedule for pumps. The user inputs the ON/OFF times in the sheet, and with some logic the sheet calculates if the pump needs to be on at the time. Problem is that to get the current time, the sheet needs to updated constantly since the time in the cell only changes automatically when the sheet is updated (either by pressing delete in an empty cell, or shift + F9 etc etc ). I will then forward the data to the PLC or factory talk view if possible. Im doing this because i couldnt find any better way to create this schedule, so please not that i am not limited to this format, any suggestions will much appreciated! Thank you Patrick

Share this post


Link to post
Share on other sites
Patrick you could try this. 1. Open the VBA Interface 2. Under ThisWorkbook find the Sub WorkBook_Open and add "Call ConstantUpdate" 3. Next Copy and Paste the following public sub into the This Workbook space. Changing Target to the Cell you Want the time in. And Waititme to the number of seconds between updates. NOTE: The DoEvents statements inside the loops are CRITICAL !! Without them things lock up. Public Sub ConstantUpdate() Dim Target As String Dim LoopExit As Boolean Dim WaitTime As Integer Dim StartTime, Endtime LoopExit = False Target = "B2" WaitTime = 5 Do Until LoopExit Range(Target).Value = Time DoEvents StartTime = Timer ' Set start time. Endtime = StartTime + WaitTime If Endtime < 86400 Then Do While Timer < Endtime DoEvents Loop Else Endtime = Endtime - 86400 Do While Timer > Endtime DoEvents Loop Do While Timer < Endtime DoEvents Loop End If Loop End Sub

Share this post


Link to post
Share on other sites
sorry for the late reply, Thank you, I will try it out as soon as i can! Patrick

Share this post


Link to post
Share on other sites
On ‎8‎/‎22‎/‎2011 at 11:21 AM, PMersault said:

VBA code resides at the display level. Drop a new command button (the gray one that can be scripted) onto a screen, then right-click and select VBA code. You'll be directed to that button's code automatically, which is currently empty. It will look like this: Private Sub Button1_Released() End Sub Before coding, you need to reference the MSExcel object model: Tools -> References Mcirosoft Excel ## Object Library (## dependent upon version) Now replace the existing code for the button with this: Private Sub Button1_Released() Excel.Application.Workbooks.Open "c:\excel.xlsx" Excel.Application.Visible = True End Sub This will open and show MSExcel with the file open. Note a few things: The excel thread will stay open until the user closes the window (you can see it as EXCEL.EXE in task manager.) I cannot remember if this will keep the VBA thread active as well, as I usually don't expose the excel GUI to the operator. If you're planning on doing any kind of programmatic manipulation, I recommend instancing the application and workbook objects, then destroying upon completion. Finally, note that the VBA code in ViewSE runs client-side, not server-side. This means that the Excel file will need to be on each client; it can't just exist at the server level unless you're mapping a drive.

I cannot find the Microsoft Excel Object under references? Any ideas why?

I didn't mean to hijack this thread however I'm trying to accomplish almost the same thing.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0