Click here to donate to keep PhreeqcUsers open
Welcome,
Guest
. Please
login
or
register
.
Did you miss your
activation email
?
1 Hour
1 Day
1 Week
1 Month
Forever
Login with username, password and session length
Forum Home
Login
Register
PhreeqcUsers Discussion Forum
»
Beginners
»
PHREEQC basics
»
Installing PHREEQC-COM and running PhreeqXcel
« previous
next »
Print
Pages: [
1
]
Go Down
Author
Topic: Installing PHREEQC-COM and running PhreeqXcel (Read 1518 times)
djb14128
Frequent Contributor
Posts: 19
Installing PHREEQC-COM and running PhreeqXcel
«
on:
October 19, 2019, 12:50:56 AM »
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
Logged
djb14128
Frequent Contributor
Posts: 19
Re: Installing PHREEQC-COM and running PhreeqXcel
«
Reply #1 on:
October 19, 2019, 12:59:12 AM »
I am using a Windows operating system.
Logged
dlparkhurst
Top Contributor
Posts: 2736
Re: Installing PHREEQC-COM and running PhreeqXcel
«
Reply #2 on:
October 19, 2019, 06:32:48 AM »
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
Logged
djb14128
Frequent Contributor
Posts: 19
Re: Installing PHREEQC-COM and running PhreeqXcel
«
Reply #3 on:
October 19, 2019, 11:34:28 AM »
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
«
Last Edit: October 19, 2019, 11:50:28 AM by djb14128
»
Logged
dlparkhurst
Top Contributor
Posts: 2736
Re: Installing PHREEQC-COM and running PhreeqXcel
«
Reply #4 on:
October 19, 2019, 04:03:12 PM »
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.
Logged
djb14128
Frequent Contributor
Posts: 19
Re: Installing PHREEQC-COM and running PhreeqXcel
«
Reply #5 on:
October 19, 2019, 05:14:57 PM »
Thank you! It works now.
Logged
Print
Pages: [
1
]
Go Up
« previous
next »
PhreeqcUsers Discussion Forum
»
Beginners
»
PHREEQC basics
»
Installing PHREEQC-COM and running PhreeqXcel