AnsweredAssumed Answered

Do you have a piece of VBA code that will auto-connect to the default server within Excel?

Question asked by Bakerr on Oct 31, 2019
Latest reply on Oct 31, 2019 by tperovic

Like • Show 0 Likes0
Comment • 6

I am using PIPUTVAL to allow operators to input sample data into the database. Everything works exactly like I would like it to on my computer. However, when I moved the Excel Workbook to the network, it stopped working. I traced the problem to their connection to the database on their computer. For some reason, their machine will not automatically connect to the default server. When I open the workbook and force a connection then run my code, it works. I don't want to get called at 4am just because they don't make the connection first. So my question is, do you have a piece of VBA code that will auto-connect to the default server?

James Devine Oct 31, 2019 4:01 PM

Correct Answer

Hi Rodney:

To connect to the default server this little code snippet should get you started:

'# DECLARE YOUR VARIABLES:

Dim myPIServer As Server

'# CONNECT TO YOUR PI SERVER:

Set myPIServer = Servers.DefaultServer '~ Optional: Servers("YourPIServerNameHere")

If myPIServer.Connected = False Then

myPIServer.Open

End If

Source: VBA Code to Connect To Your PI Server and Output the Name

NOTE: You will first need to add the following "references" in your VBA Code. This is done by simply navigating to [Tools]=>[References]. This launches the "References - VBA Project" dialog box. Here you scroll and then check "PISDK 1.3 Type Library" and "PISDKCommon 1.0 Type Library".

See the reply in context

No one else had this question

Outcomes

MJarrell10 Oct 31, 2019 2:49 PM

Could you potentially post your code so we may take a look at it?

Actions

Bakerr @ MJarrell10 on Oct 31, 2019 3:04 PM

There are 6 different areas that basically have the same code. When the code fails, it fails on the PUTVAL line. I know that there is nothing wrong with the code because it works when I simply go into the Connection Manager and connect. I just assume that since the workbook is saved to a Network location, when a new computer opens up the workbook, the connection gets confused. I have 3 more departments to write this kind of code for, so to avoid some very foolish confusion, I would rather put a line of VBA at the beginning of this code that "checks" the connection to the server.

Sub EQ_BASIN_SAMPLES()

Dim tagname As String 'Current PI Tag being used

Dim value As Range 'Current PI Tags value to be saved

Dim timestamp As String 'Current timestamp being used

Dim piserver As String 'PI Server

Dim outcell As Range 'Cell to place results of PUTVAL save

Dim Daystamp As String 'Daytime timestamp

Dim Nightstamp As String 'Nighttime timestamp

Dim i As Integer 'Todays start row of data on EQ page

Dim j As Integer 'Todays end row of data on EQ page

Dim Basin As String 'Basin for current row of data on EQ page

Dim t As Integer 'Used to determine how many rows of new data was entered today

Dim u As Integer 'EQAs daytime or nighttime samples

Dim v As Integer 'EQBs daytime or nighttime samples

Dim w As Integer 'EQCs daytime or nighttime samples

Dim x As Integer 'Start row of PI Tags on PI Tags page

Dim y As Integer 'Start column of Values on EQ page, same as t but I needed another calculation

i = 0

j = 0

t = 1

u = 0

v = 0

w = 0

x = 0

y = 7

i = Sheets("PI Tag").Cells(2, 52).value 'EQ program writes the start row for today to PI Tag page

j = i 'Use start row (i) to find end row (j) by seeing if column 7 (F) is empty

While t < 10 'There are only 9 available rows for the Basins

If Sheets("EQ").Cells(j, 6).value > 0 Then

j = j + 1

End If

t = t + 1

Wend

'Set up base info for PUTVAL command

Daystamp = Sheets("PI Tag").Cells(2, 49).value

Nightstamp = Sheets("PI Tag").Cells(2, 50).value

piserver = Sheets("PI Tag").Cells(2, 51).value

While i < j

Basin = Sheets("EQ").Cells(i, 6).value 'Discover which EQ Basin for current row

y = 7

'Use Basin info to set up where PI Tags (x) will start on PI Tag page. (u, v, or w) will determine if we use daytime timestamp or nighttime timestamp.

If Basin = "EQA" Then

x = 5

u = u + 1

End If

If Basin = "EQB" Then

x = 9

v = v + 1

End If

If Basin = "EQC" Then

x = 13

w = w + 1

End If

While y < 11

tagname = Sheets("PI Tag").Cells(x, 2).value

If Basin = "EQA" And u = 1 Then

timestamp = Daystamp

End If

If Basin = "EQA" And u > 1 Then

timestamp = Nightstamp

End If

If Basin = "EQB" And v = 1 Then

timestamp = Daystamp

End If

If Basin = "EQB" And v > 1 Then

timestamp = Nightstamp

End If

If Basin = "EQC" And w = 1 Then

timestamp = Daystamp

End If

If Basin = "EQC" And w > 1 Then

timestamp = Nightstamp

End If

Set value = Sheets("EQ").Cells(i, y)

Set outcell = Sheets("PI Tag").Cells(x, 6)

macroResult = Application.Run("PIPutval", tagname, value, timestamp, piserver, outcell)

t = t + 1

x = x + 1

y = y + 1

Wend

i = i + 1

Wend

End Sub

Actions

MJarrell10 @ Bakerr on Oct 31, 2019 4:10 PM

We had a very similar issue and it turned out to be that the PI Visual suite was not installed on the device we were trying to use. Once we installed the suite and ensured that the mapping was correct, it started working for us.

Actions

James Devine Oct 31, 2019 4:01 PM

Hi Rodney:

To connect to the default server this little code snippet should get you started:

'# DECLARE YOUR VARIABLES:

Dim myPIServer As Server

'# CONNECT TO YOUR PI SERVER:

Set myPIServer = Servers.DefaultServer '~ Optional: Servers("YourPIServerNameHere")

If myPIServer.Connected = False Then

myPIServer.Open

End If

Source: VBA Code to Connect To Your PI Server and Output the Name

Actions

Bakerr Oct 31, 2019 4:16 PM

Thank you both for your help. This is exactly what I wanted. I knew that it would be something quick and easy.

Actions

tperovic Oct 31, 2019 5:36 PM

Yeah, but the Default Server can be changed using the PI SDK Utility:

That burned me a few months ago. If a user can do something, they will.

An Excel spreadsheet suddenly stopped working: Tag not found.

I put the server name in a cell, along with other configuration information, then got the name from there.

Dim piserver As Server

Set piserver = Servers(Range("Setup!$B$1"))

If piserver.Connected = False Then

piserver.Open

End If

I password-protected the worksheet for good measure.

Tony Perovic
Compumation, Inc.

Actions

6 Replies

MJarrell10 Oct 31, 2019 2:49 PM
Mark Correct
Correct Answer
Could you potentially post your code so we may take a look at it?
Like • Show 0 Likes0
Actions
- Bakerr @ MJarrell10 on Oct 31, 2019 3:04 PM
  Mark Correct
  Correct Answer
  There are 6 different areas that basically have the same code. When the code fails, it fails on the PUTVAL line. I know that there is nothing wrong with the code because it works when I simply go into the Connection Manager and connect. I just assume that since the workbook is saved to a Network location, when a new computer opens up the workbook, the connection gets confused. I have 3 more departments to write this kind of code for, so to avoid some very foolish confusion, I would rather put a line of VBA at the beginning of this code that "checks" the connection to the server.
  
  Sub EQ_BASIN_SAMPLES()
  
  Dim tagname As String   'Current PI Tag being used
  
  Dim value As Range       'Current PI Tags value to be saved
  
  Dim timestamp As String 'Current timestamp being used
  
  Dim piserver As String   'PI Server
  
  Dim outcell As Range     'Cell to place results of PUTVAL save
  
  Dim Daystamp As String   'Daytime timestamp
  
  Dim Nightstamp As String 'Nighttime timestamp
  
  Dim i As Integer         'Todays start row of data on EQ page
  
  Dim j As Integer         'Todays end row of data on EQ page
  
  Dim Basin As String     'Basin for current row of data on EQ page
  
  Dim t As Integer         'Used to determine how many rows of new data was entered today
  
  Dim u As Integer         'EQAs daytime or nighttime samples
  
  Dim v As Integer        'EQBs daytime or nighttime samples
  
  Dim w As Integer         'EQCs daytime or nighttime samples
  
  Dim x As Integer         'Start row of PI Tags on PI Tags page
  
  Dim y As Integer         'Start column of Values on EQ page, same as t but I needed another calculation
  
  i = 0
  
  j = 0
  
  t = 1
  
  u = 0
  
  v = 0
  
  w = 0
  
  x = 0
  
  y = 7
  
  i = Sheets("PI Tag").Cells(2, 52).value 'EQ program writes the start row for today to PI Tag page
  
  j = i 'Use start row (i) to find end row (j) by seeing if column 7 (F) is empty
  
  While t < 10 'There are only 9 available rows for the Basins
  
     If Sheets("EQ").Cells(j, 6).value > 0 Then
  
         j = j + 1
  
     End If
  
     t = t + 1
  
  Wend
  
  'Set up base info for PUTVAL command
  
  Daystamp = Sheets("PI Tag").Cells(2, 49).value
  
  Nightstamp = Sheets("PI Tag").Cells(2, 50).value
  
  piserver = Sheets("PI Tag").Cells(2, 51).value
  
  While i < j
  
     Basin = Sheets("EQ").Cells(i, 6).value 'Discover which EQ Basin for current row
  
     y = 7
  
  
  
     'Use Basin info to set up where PI Tags (x) will start on PI Tag page. (u, v, or w) will determine if we use daytime timestamp or nighttime timestamp.
  
     If Basin = "EQA" Then
  
         x = 5
  
         u = u + 1
  
     End If
  
     If Basin = "EQB" Then
  
         x = 9
  
         v = v + 1
  
     End If
  
     If Basin = "EQC" Then
  
         x = 13
  
         w = w + 1
  
     End If
  
     While y < 11
  
         tagname = Sheets("PI Tag").Cells(x, 2).value
  
         If Basin = "EQA" And u = 1 Then
  
             timestamp = Daystamp
  
         End If
  
         If Basin = "EQA" And u > 1 Then
  
             timestamp = Nightstamp
  
         End If
  
         If Basin = "EQB" And v = 1 Then
  
             timestamp = Daystamp
  
         End If
  
         If Basin = "EQB" And v > 1 Then
  
             timestamp = Nightstamp
  
         End If
  
         If Basin = "EQC" And w = 1 Then
  
             timestamp = Daystamp
  
         End If
  
         If Basin = "EQC" And w > 1 Then
  
             timestamp = Nightstamp
  
         End If
  
         Set value = Sheets("EQ").Cells(i, y)
  
         Set outcell = Sheets("PI Tag").Cells(x, 6)
  
         macroResult = Application.Run("PIPutval", tagname, value, timestamp, piserver, outcell)
  
         t = t + 1
  
         x = x + 1
  
         y = y + 1
  
     Wend
  
  i = i + 1
  
  Wend
  
  End Sub
  Like • Show 0 Likes0
  Actions
  - MJarrell10 @ Bakerr on Oct 31, 2019 4:10 PM
    Mark Correct
    Correct Answer
    We had a very similar issue and it turned out to be that the PI Visual suite was not installed on the device we were trying to use. Once we installed the suite and ensured that the mapping was correct, it started working for us.
    Like • Show 0 Likes0
    Actions
James Devine Oct 31, 2019 4:01 PM
Unmark Correct
Correct Answer
Hi Rodney:

To connect to the default server this little code snippet should get you started:

'# DECLARE YOUR VARIABLES:

    Dim myPIServer As Server



    '# CONNECT TO YOUR PI SERVER:

    Set myPIServer = Servers.DefaultServer  '~ Optional: Servers("YourPIServerNameHere")

    If myPIServer.Connected = False Then

        myPIServer.Open

    End If

Source: VBA Code to Connect To Your PI Server and Output the Name

NOTE: You will first need to add the following "references" in your VBA Code. This is done by simply navigating to [Tools]=>[References]. This launches the "References - VBA Project" dialog box. Here you scroll and then check "PISDK 1.3 Type Library" and "PISDKCommon 1.0 Type Library".
Like • Show 0 Likes0
Actions
Bakerr Oct 31, 2019 4:16 PM
Mark Correct
Correct Answer
Thank you both for your help. This is exactly what I wanted. I knew that it would be something quick and easy.
Like • Show 0 Likes0
Actions
tperovic Oct 31, 2019 5:36 PM
Mark Correct
Correct Answer
Yeah, but the Default Server can be changed using the PI SDK Utility:

That burned me a few months ago. If a user can do something, they will.

An Excel spreadsheet suddenly stopped working: Tag not found.

I put the server name in a cell, along with other configuration information, then got the name from there.

   Dim piserver As Server

   Set piserver = Servers(Range("Setup!$B$1"))

   If piserver.Connected = False Then

    piserver.Open

   End If

I password-protected the worksheet for good measure.

Tony Perovic
Compumation, Inc.
Like • Show 1 Like1
Actions

Do you have a piece of VBA code that will auto-connect to the default server within Excel?

Outcomes

Related Content

Recommended Content