Installing PHREEQC-COM and running PhreeqXcel

[djb14128]

Hi All,

I have looked at different ways to make the code work, but I still keep getting the error message "Run-time error '424': Object required". Is there a way to overcome this problem?

Here is the following code:
Sub RunPhreeqc(Optional Server As Boolean)
  ' Call examples: RunPhreeqc or RunPhreeqc False or Call RunPhreeqc() or Call RunPhreeqc(False)
  ' No user messages if Server = True (default = False)
 
 
  ' Requirements (installed items):
  ' - PHREEQC COM-module (version 3, latest download from USGS website)
  ' - MS Forms library (part of Windows OS)
  ' MS-Windows clipboard is used for fast copy/paste actions
  ' This requires (MSForms.)DataObject from the MS Forms Library
  ' For loading this library: include Forms in the VBA project,
  ' and select MS Forms library in References under menu option Tools
  ' included Form might be removed afterwards
 
  ' Initialize application, including COM-object
  Dim starttime, endtime As Single
  starttime = Timer
 
  On Error Resume Next                         ' ChDrive gives error running on a UNC-system (i.e. Citrix)
  ChDrive Left(ActiveWorkbook.Path, 1)         ' set drive of Excel file as default drive
  ChDir ActiveWorkbook.Path                    ' set path of Excel file as default directory
  On Error GoTo ErrHandler:                    ' set Error handler
 
  Set phreeqc = CreateObject("IPhreeqcCOM.Object")
  phreeqc.OutputStringOn = True                ' default = False
  phreeqc.OutputFileOn = False                 ' default value
  phreeqc.SelectedOutputFileOn = False         ' default value
  phreeqc.CurrentSelectedOutputUserNumber = 1  ' default = 1
 
  Worksheets("Messages").Activate
  ActiveSheet.Cells.ClearContents              ' clear (old) Messages
  InputSheet = Worksheets("Run_Control").Range("InputSheet").Value
  DatabaseSheet = Worksheets("Run_Control").Range("DatabaseSheet").Value
  ReturnSheet = Worksheets("Run_Control").Range("ReturnSheet").Value
  ' User messages only in non-server mode
  If Not Server And Not Worksheets("Run_Control").Range("Messages").Value Then
    Server = True
  End If
 
 
  ' Define input file and database file (might be empty)
  File_In = Worksheets("Run_Control").Range("InputFile").Value
  ' Iphreeqc generates an error if File_In can not be read
  DatabaseFile = Worksheets("Run_Control").Range("DatabaseFile").Value
  ' Iphreeqc generates an error if DatabaseFile can not be read
  ' Include full path name if not in default directory (= location of Excel file)
   
  ' Load database in COM-object
  If DatabaseFile <> "" Then  'use DatabaseFile if filled
     phreeqc.LoadDatabase (DatabaseFile)  'method loads database (returns number of errors)
     
     ' database dump to sheet Database (with switch True/False for users preference)
     If True Then  'allways fast version
        ' Import database file in Excel-sheet (fast version, requires MS Forms Library)
        ' Fast copy/paste from database file via Clipboard (with TAB = next column, new rows etc)
        Worksheets(DatabaseSheet).Activate
        ActiveSheet.Cells.ClearContents
        Open DatabaseFile For Input As #2
        Dim MyData2 As MSForms.DataObject  'from MS Forms Library
        Set MyData2 = New MSForms.DataObject
        MyData2.SetText Input(LOF(2), #2)
        MyData2.PutInClipboard
        Close #2
        ActiveSheet.Paste Destination:=Worksheets(DatabaseSheet).Range("A1")
     End If
  Else  ' use DatabaseSheet
     ' From sheet Database into string for COM-module
     ' Copy content sheet Database to Clipboard
     Sheets(DatabaseSheet).Select
     Cells.Select
     Selection.Copy
     ' Copy Clipboard to String
     Dim IstringDB As String
     Dim MyData1 As MSForms.DataObject  'from MS Forms Library
     Set MyData1 = New MSForms.DataObject
     MyData1.GetFromClipboard
     IstringDB = MyData1.GetText   'or GetText(1)
     ' use string in COM-module
     phreeqc.LoadDatabaseString (IstringDB)  'method loads database (returns number of errors)
  End If
  ' Iphreeqc generates an error if Database is not loaded in COM-object
  ' Iphreeqc generates an error if file Db cannot be opened or read (as proper PHREEQC database)
  ' Iphreeqc ignores the database file specified with DATABASE in the input file (.pqi)
  ' This is also shown in the warning in phreeqc.out:
  '  DATABASE <filename>
  '  WARNING: DATABASE keyword is ignored by IPhreeqc.
   
   
  ' Run PHREEQC with user input
  If File_In <> "" Then  'use File_In if filled
     phreeqc.RunFile (File_In)  'method runs PHREEQC (returns number of errors)
     
     ' input file dump to sheet Input (with switch True/False for users preference)
     If True Then  'allways fast version
        ' Import input file in Excel-sheet (fast version, requires MS Forms Library)
        ' Fast copy/paste from database file via Clipboard (with TAB = next column, new rows etc)
        Worksheets(InputSheet).Activate
        ActiveSheet.Cells.ClearContents
        Open File_In For Input As #3
        Dim MyData3 As MSForms.DataObject  'from MS Forms Library
        Set MyData3 = New MSForms.DataObject
        MyData3.SetText Input(LOF(3), #3)
        MyData3.PutInClipboard
        Close #3
        ActiveSheet.Paste Destination:=Worksheets(InputSheet).Range("A1")
     End If
  Else  ' use InputSheet as String
     
     Dim Istring As String
     ' Content from from InputSheet into Clipboard
     Worksheets(InputSheet).Activate
     Cells.Select
     Selection.Copy
     ' Copy Clipboard to String
     Dim MyData4 As MSForms.DataObject  'from MS Forms Library
     Set MyData4 = New MSForms.DataObject
     MyData4.GetFromClipboard
     Istring = MyData4.GetText   'or GetText(1)
     phreeqc.RunString (Istring) 'method runs PHREEQC (returns number of errors)
  End If
 
 
  ' Save output string in phreeqc.out sheet (fast version, requires MS Forms Library)
  ' Fast copy/paste from outputstring via Clipboard (with TAB = next column, new rows etc)
  Worksheets("phreeqc.out").Activate
  ActiveSheet.Cells.ClearContents
  Dim MyData As MSForms.DataObject  'from MS Forms library
  Set MyData = New MSForms.DataObject
  MyData.SetText phreeqc.GetOutputString()
  MyData.PutInClipboard
  ActiveSheet.Paste Destination:=Worksheets("phreeqc.out").Range("A1")
 
 
  ' Save Selected_Output data to output sheets Output, Output2, Output 3 etc.
  Dim num, nums
  nums = phreeqc.GetNthSelectedOutputUserNumberList()
  For Each num In nums
     phreeqc.CurrentSelectedOutputUserNumber = num
     If num = "1" Then num = ""  'Name OutputSheet is Output (n=1) else Output<n>
     OutputSheet = "Output" & num
     Worksheets(OutputSheet).Activate
     ActiveSheet.Cells.ClearContents
     ' check for empty output array to avoid Excel error in Range copy
     If phreeqc.GetSelectedOutputValue(0, 0) <> "" Then
        arr = phreeqc.GetSelectedOutputArray()
        Range(Cells(1, 1), Cells(phreeqc.RowCount, phreeqc.ColumnCount)) = arr
     End If
  Next

 
  ' Messages
  If Server = False Then  'no messageboxes in server
     endtime = Timer
     MsgBox ("Phreeqc ran successfully." + Chr(13) + "IPreeqcCOM " + phreeqc.Version + Chr(13) + "End of Run after  " + Format((endtime - starttime), "#,##0.00 "" Seconds."""))
  End If
  If phreeqc.GetWarningString() <> "" Then
     Worksheets("Messages").Activate
     Cells(1, 1) = "Phreeqc errors: " & phreeqc.GetWarningString()
     If Server = False Then  'no messageboxes in server
        MsgBox phreeqc.GetWarningString()  'PHREEQC warnings (from COM-object)
     End If
  End If
 
 
  ' Show Excel return sheet
  On Error Resume Next                    ' Prevents VBA-error if ReturnSheet does not exist
  Worksheets(ReturnSheet).Activate
Exit Sub
   
   
ErrHandler:
  If Server = False Then  'no messageboxes in server
     If phreeqc.GetErrorString() = "" Then      'This line is highlighted in yellow in VBA.
        MsgBox ("Iphreeqc error:" + Chr$(13) + "Excel-VBA error")
     Else
        MsgBox ("Phreeqc errors: " + Chr$(13) + phreeqc.GetErrorString())
     End If
  End If
  Worksheets("Messages").Activate
  If phreeqc.GetErrorString() = "" Then
     Cells(3, 1) = "Iphreeqc error:" + Chr$(13) + "Excel-VBA error"
  Else
     Cells(3, 1) = "Phreeqc errors: " + Chr$(13) + phreeqc.GetErrorString()
  End If

End Sub

I appreciate the help.

Regards,
djb14128

[djb14128]

I am using a Windows operating system.

[dlparkhurst]

It may be that you need a different PHREEQC COM. For starters, make sure that you install both 32 and 64 bit versions of the COM.

https://water.usgs.gov/water-resources/software/PHREEQC/IPhreeqcCOM-3.5.0-14000-win32.msi

https://water.usgs.gov/water-resources/software/PHREEQC/IPhreeqcCOM-3.5.0-14000-x64.msi

[djb14128]

Hi,

I think I've got both IPhreeqcCOM x64 3.5.0- 14000 and IPhreeqcCOM 3.5.0-14000 installed. I'm on the setup boxes for both of them and I can see the Change, Repair and Remove buttons. I can't click the Change button, but I can click the Repair and Remove buttons. Thanks.

Regards,
djb14128

[dlparkhurst]

OK, so you have them both installed.

You may not have the necessary .net 3.5. See the following posts.


http://phreeqcusers.org/index.php/topic,687.msg2164.html#msg2164


If you are still having problems, it is easier for us to debug if you use the COM example, usually found in this location.

C:\Program Files\USGS\IPhreeqcCOM 3.5.0- 14000\examples\com\excel\runphreeqc.xls

Open the xls file, run the runphreeqc macro, and see what happens.

[djb14128]

Thank you! It works now.