10 Replies Latest reply on Mar 18, 2015 4:34 PM by rkoonce Branched to a new discussion.

    Datalink link update on excel open

    samirmohanty

      I have created a batch file which opens the excel file and close it that have the PI tag values. I want that when the excel opens through this batch file, it should update on same time. I have used the hourly data for today, so as the batch file runs on each hour scheduled, it should update with hour value. But when the excel opens in my case, it does not come in PI view with update mode. Do anyone suggest what i should write in vb\excel micro for the same.

        • Re: Datalink link update on excel open
          dng

          Hi Samir,

           

          Do you want a one-time calculation when your Excel file is open? If so, can you put in code to trigger a full calculation whenever the workbook is opened?

           

          Private Sub Workbook_Open()
              Application.CalculateFull
          End Sub
          

           

          If you only want the calculation to occur whenever your batch file opens the excel file, the above code will work. However, let me know if you intention is instead to turn on the automatic calculation option instead (e.g. if you are going to keep the file opened for a while).

            • Re: Datalink link update on excel open
              samirmohanty

              This is not working in my case, so i went on another solution to define the start and end time and given the timestamp,it works, but the file is not generating for semi colon as seperator. It genrates with comma.

              However, in normal excel the semi-colon seperator works, but not the one with PI.

            • Re: Datalink link update on excel open
              pthivierge

              Hello Samir,

               

              Did Daphne's proposed solution worked for you?

              If so you can mark it as correct answer, otherwise please let us know if you if you have more questions or what you did to update your values when excel starts.

               

              Regards,

              • Re: Datalink link update on excel open
                pthivierge

                Hello Samir,

                 

                Can you provide us the content of the .bat files and the VBA code you have so far? That may help us to better understand.

                --

                The values delimiter ("," or ";") is normally determined by regional settings of the computer. But when exporting it through VBA, like the example below, whatever the delimiter you have configured on the machine the format used is always the coma ",".

                Public Sub Export()

                    ThisWorkbook.ActiveSheet.SaveAs "c:\temp\data.csv", xlCSV

                End Sub

                So I assume that you are exporting your data using VBA.

                 

                I would recommend that you add an additional function to your VBA code so you control totally your data export.

                You can find an example here, the ExportToTextFile function should do what you need.

                 

                Then you can use it like this:

                ExportToTextFile FName:="C:\temp\data.csv", Sep:=";", SelectionOnly:=False, AppendData:=False

                 

                I hope this helps.

                  • Re: Datalink link update on excel open
                    samirmohanty

                    Yes, I am exporting it through VBA and code is:

                    Application.CutCopyMode = False

                        Application.DisplayAlerts = False

                      

                        Filename = "test.csv"

                      

                        path = ThisWorkbook.path & Application.PathSeparator & Filename

                        ActiveWorkbook.SaveAs Filename:= _

                            path, FileFormat:= _

                            xlCSV, CreateBackup:=False

                        ActiveWindow.Close

                        Application.DisplayAlerts = True

                     

                    Please suggest where do i use this sep in my code.

                      • Re: Datalink link update on excel open
                        dng

                        Hi Samir,

                         

                        If you are interested in Patrice's example, copy the ExportToTextFile function from this link to include in your code. Then change your VBA code to the following:

                         

                            Application.CutCopyMode = False
                            Application.DisplayAlerts = False
                        
                            Filename = "test.csv"
                        
                            Path = ThisWorkbook.Path & Application.PathSeparator & Filename
                            ExportToTextFile FName:=Path, Sep:=";", SelectionOnly:=False, AppendData:=False
                            ActiveWindow.Close
                            Application.DisplayAlerts = True
                        
                          • Re: Datalink link update on excel open
                            samirmohanty

                            When I am using this code, then after the value, rows goes upto 999, though i do not have that values. How can we restrict it to only last value we get.

                              • Re: Datalink link update on excel open
                                dng

                                If your input argument for SelectionOnly is False, the ExportToTextFile function will automatically select only the used range of your worksheet to export. Can you double check the used range of your worksheet? You might also want to reset your used range before calling ExportToTextFile. Please check out this online discussion here.
                                Alternatively, if the number of columns and rows or your worksheet if fixed, you can manually select the range before calling ExportToTextFile, and set the SelectionOnly argument to True.

                                  • Re: Datalink link update on excel open
                                    rkoonce

                                    Hello Samir,

                                     

                                    I believe you need to execute DLResize on these arrays. If so, you can search that term here for bountiful results, or try this one out: DLResize

                                     

                                    Cheers!

                                    Richard

                                     

                                    PS Please use the "Like" and "Helpful' options in our forums, when appropriate. It can help others when "skimming" the conversation threads.