Hey all. I am getting a “unable to compile error” when I try to execute my VB code. Any ideas? I have already written macros and tested them on excel and they work fine. I want VB to call each macro independently. Below is my code:
Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting
Public Class TestClass
Implements IScriptObject
Private Host As IScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Get
Return Host
End Get
Set(ByVal value As IScriptingHost)
Host = value
End Set
End Property
Public Sub Execute() Implements IScriptObject.Execute
Option Explicit
Dim xlApp
Dim xlBook
Dim wbPath
Set xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts=False
wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
'~~> Change for Worklist Generator excel workbook Path here
Set xlBook = xlApp.Workbooks.Open(wbPath)
'execute command to import file name
xlApp.Run "ImportVariables"
xlApp.Run "ImportUVFiles"
xlApp.Run "CreateWorklist"
xlApp.Run "ExportWorklist"
xlApp.Run "ExportPlateReads"
'Close excel or allow it to remain open
If Dir(wbPath) <> "" Then
Kill wbPath
End If
xlBook.SaveAs wbPath
xlApp.DisplayAlerts=True
xlBook.Close
xlApp.Quit
Set xlBook = Nothing
Set xlApp = Nothing
End Sub
Can you try the following in the block after Public Sub
Dim xlApp
Dim xlBook
Dim wbPath
xlApp = CreateObject("Excel.Application")
xlApp.DisplayAlerts = False
wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
'~~> Change for Worklist Generator excel workbook Path here
xlBook = xlApp.Workbooks.Open(wbPath)
'execute command to import file name
xlApp.Run("ImportVariables")
xlApp.Run("ImportUVFiles")
xlApp.Run("CreateWorklist")
xlApp.Run("ExportWorklist")
xlApp.Run("ExportPlateReads")
'Close excel or allow it to remain open
If Dir(wbPath) <> "" Then
Kill(wbPath)
End If
xlBook.SaveAs(wbPath)
xlApp.DisplayAlerts = True
xlBook.Close
xlApp.Quit
xlBook = Nothing
xlApp = Nothing
Doesn’t the command Kill wbPath try to remove a file that was opened in an earlier command Set xlBook = xlApp.Workbooks.Open(wbPath)? That would be an issue.
The line saying Option explicit certainly doesn’t belong there at the first line after Public Sub Execute().
If I take a working VB file and add Option Explicit at the same place it also gives a context-check error in FluentControl saying “unable to load and compile…”. Also in Visual Studio there is a VB .NET syntax check error when adding Option explicit inside any Sub (Statement is not valid inside a method)
So first thing, I don’t have a Tecan and have no way of trying this, but this is how I would code it just from a VB point of view:
Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting
Public Class TestClass Implements IScriptObject
Private Host As IScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Get
Return Host
End Get
Set(ByVal value As IScriptingHost)
Host = value
End Set
End Property
Public Sub Execute() Implements IScriptObject.Execute
Dim xlApp As New Excel.Application ' Unless you need late binding, then do this: Dim xlApp As Object = CreateObject("Excel.Application")
Dim xlBook As Excel.Workbook
Dim wbPath As String = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
xlApp.DisplayAlerts = False
xlBook = xlApp.Workbooks.Open(wbPath)
xlApp.Run("ImportVariables")
xlApp.Run("ImportUVFiles")
xlApp.Run("CreateWorklist")
xlApp.Run("ExportWorklist")
xlApp.Run("ExportPlateReads")
xlBook.SaveAs(wbPath)
xlApp.DisplayAlerts = True
xlBook.Close()
xlApp.Quit()
'Why delete it after you've created it?
' If IO.File.Exists(wbPath) Then
' IO.File.Delete(wbPath)
' End If
xlBook = Nothing
xlApp = Nothing
End Sub
End Class
Can you try that?
As solomon said, you are deleting the file after you save it, which is probably not what you want.
Option Explicit forces the explicit declaration of all variables in your code, but needs to be place in the module level (i.e. at the top of the file), not the sub/function level. It is safer to use, as it makes sure you declare all your variables, but in this case can hinder debugging.
i was able to get vb without FluentControl giving an invalid error,
try this out,
‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’
‘Filename:
‘Version:
‘Created:
‘Initial Author: Daniel Leach
‘Purpose:
‘Requires:
‘FluentControl variables in:
’
’
’
’
‘FluentControl variables out:
’
’
’
’
‘’’’’’’’’’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’‘’
Imports Microsoft.VisualBasic
Imports System
Imports Tecan.Core.Scripting
Imports System.Collections
Imports System.IO
Imports System.Text
Public Class Test
Implements IScriptObject
Private Host As IScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Get
Return Host
End Get
Set(ByVal value As IScriptingHost)
Host = value
End Set
End Property
Public Sub Execute() Implements IScriptObject.Execute
'Public Sub Execute() Implements IScriptObject.Execute
'Option Explicit
Dim wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
Dim xlApp = CreateObject("Excel.Application")
Dim xlBook = xlApp.Workbooks.Open(wbPath)
xlApp.DisplayAlerts = False
'Set xlApp = CreateObject("Excel.Application")
'xlApp.DisplayAlerts=False
'wbPath = "C:\Tecan Files\Plate Readouts\SMW Chase Pooling Effort.xlsm"
'~~> Change for Worklist Generator excel workbook Path here
'Set xlBook = xlApp.Workbooks.Open(wbPath)
'execute command to import file name
'xlApp.Workbooks.Open("C:\Tecan Files\Plate Readouts\TempStorage.xlsb")
xlApp.Run("ImportVariables")
xlApp.Run("ImportUVFiles")
xlApp.Run("CreateWorklist")
xlApp.Run("ExportWorklist")
xlApp.Run("ExportPlateReads")
'Close excel or allow it to remain open
'If Dir(wbPath) <> "" Then
' Kill wbPath
'End If
xlBook.Save 'As wbPath
'xlApp.DisplayAlerts=True
xlBook.Close
xlApp.Quit
'Set xlBook = Nothing
'Set xlApp = Nothing
End Sub
Hey! Thanks for your help! how did you test it? is there anything that is commented out that i Need to “re-enable”? I tried it on my end and nothing happened.
I tried to disable most of the commands and see if it could just open an excel file and it doesnt. This is that Public Sub section
Dim wbPath = “C:\Tecan Files\Plate Readouts\Chase Collection Plate Readout.xlsm”
Dim xlApp = CreateObject(“Excel.Application”)
Dim xlBook = xlApp.Workbooks.Open(wbPath)
xlApp.DisplayAlerts = False
these are my errors when I open it in VSCode and run it:
vbc : error BC30420: ‘Sub Main’ was not found in ‘Chase_Pooling_Macro’.
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(3) : warning BC40056: Namespace or type specified in the Imports ‘Tecan.Core.Scripting’ doesn’t contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn’t use any aliases.
Imports Tecan.Core.Scripting
~~~~~~~~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(8) : error BC30002: Type ‘IScriptObject’ is not defined.
Implements IScriptObject
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(10) : error BC30002: Type ‘IScriptingHost’ is not defined.
Private Host As IScriptingHost
~~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(12) : error BC30002: Type ‘IScriptingHost’ is not defined.
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
Public Property ScriptingHost() As IScriptingHost Implements IScriptObject.ScriptingHost
~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(20) : error BC30002: Type ‘IScriptingHost’ is not defined.
Set(ByVal value As IScriptingHost)
~~~~~~~~~~~~~~
c:\Tecan Files\Plate Readouts\Chase_Pooling_Macro.vb(30) : error BC30002: Type ‘IScriptObject’ is not defined.
Public Sub Execute() Implements IScriptObject.Execute
Yeah I also didnt get errors when I ran in fluent control, but it did not do anything. I even edited out everything as comments and just tried to open wbPath and nothing happened.
Are you able to use this framework to open an excel sheet?
Public Sub Execute() Implements IScriptObject.Execute
Dim wbPath = “C:\Optimize.LLC\HTPD DEVELOPMENT\CHROMATOGRAPHY Fraction Plate Visualization Tool (v1.00).xlsm”
Dim xlApp = CreateObject(“Excel.Application”)
Dim xlBook = xlApp.Workbooks.Open(wbPath)
xlApp.DisplayAlerts = True
xlApp.Visible = True
xlApp.Run("ResetWells")
xlApp.Run("WellColouring")
xlBook.Save
xlApp.DisplayAlerts=False
xlBook.Close
xlApp.Quit
End Sub
looks like you might have permission issues in executing macro’s “remotely” from Excel
