VBScript to VBA?

B

Barry

I have imported my sample (.vbs) script into Access' Visual Basic
Editor, and ran as is (see script below post). Errors came up...the first
of which stated "Compile error: Invalid outside procedure", so I looked
it up in the Help section and added "Property" to second line (of the
actual script, under header), and ran again...then an error stated
"Compile error: Argument required for Property Let or Property Set", but I
did not understand this Help section. The "=" was highlighted on the
second line of script, in this instance.

Can anyone please look at my sample script below and help me to adjust
so that I can run the outside process from within Access (2003)?

Script starts below:

'
---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
'
---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data
Management Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,

"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0",
"0"
 
D

Dirk Goldgar

Barry said:
I have imported my sample (.vbs) script into Access' Visual Basic
Editor, and ran as is (see script below post). Errors came up...the first
of which stated "Compile error: Invalid outside procedure", so I looked
it up in the Help section and added "Property" to second line (of the
actual script, under header), and ran again...then an error stated
"Compile error: Argument required for Property Let or Property Set", but I
did not understand this Help section. The "=" was highlighted on the
second line of script, in this instance.

Can anyone please look at my sample script below and help me to adjust
so that I can run the outside process from within Access (2003)?

Script starts below:

'
---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
'
---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data
Management Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,

"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0",
"0"
Click to expand...


Barry, your script must be inside a Sub or a Function. Enter the line

Sub TableToFeature()

before the first line of the script, and the line

End Sub

after the last line of the script. Feel free to give the Sub some other
name than "TableToFeature", which I've arbitrarily used above. Make sure
that the Sub and the module itself don't have the same name.

That should at least get you started.
 
B

Barry

Thank you very much, Dirk. I took your advice and it did apparently solve
the error received previously.

After inserting the beginning and ending lines, and running script again, an
error stated "Run-time error '424': Object required", at the line "Set gp =
WScript.CreateObject("esriGeoprocessing.GPDispatch.1")".

I welcome any assistance, please. We have nobody in my office that is
either a programmer or knowledgeable with scripts. I guess I will eventually
have to take some courses in it.
--
Barry Guidry


Dirk Goldgar said:
Barry said:
I have imported my sample (.vbs) script into Access' Visual Basic
Editor, and ran as is (see script below post). Errors came up...the first
of which stated "Compile error: Invalid outside procedure", so I looked
it up in the Help section and added "Property" to second line (of the
actual script, under header), and ran again...then an error stated
"Compile error: Argument required for Property Let or Property Set", but I
did not understand this Help section. The "=" was highlighted on the
second line of script, in this instance.

Can anyone please look at my sample script below and help me to adjust
so that I can run the outside process from within Access (2003)?

Script starts below:

'
---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 22 2008 12:22:58 PM
' (generated by ArcGIS/ModelBuilder)
'
---------------------------------------------------------------------------

' Create the Geoprocessor object
set gp = WScript.CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data
Management Tools.tbx"


' Local variables...
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells = "D:\test\Well_Annotation.mdb\XYWells"
XYWells_end = "D:\test\Well_Annotation.mdb\XYWells_end"
XYWells_Layer = "XYWells_Layer"

' Process: Copy Rows...
gp.CopyRows_management Well_test, XYWells, ""

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management XYWells, "EASTING", "NORTHING",
XYWells_Layer,

"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.CopyFeatures_management XYWells_Layer, XYWells_end, "", "0", "0",
"0"
Click to expand...


Barry, your script must be inside a Sub or a Function. Enter the line

Sub TableToFeature()

before the first line of the script, and the line

End Sub

after the last line of the script. Feel free to give the Sub some other
name than "TableToFeature", which I've arbitrarily used above. Make sure
that the Sub and the module itself don't have the same name.

That should at least get you started.

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
D

Dirk Goldgar

Barry said:
Thank you very much, Dirk. I took your advice and it did apparently solve
the error received previously.

After inserting the beginning and ending lines, and running script again,
an
error stated "Run-time error '424': Object required", at the line "Set gp
=
WScript.CreateObject("esriGeoprocessing.GPDispatch.1")".

I welcome any assistance, please. We have nobody in my office that is
either a programmer or knowledgeable with scripts. I guess I will
eventually
have to take some courses in it.
Click to expand...


In VBA, you don't need the WScript qualifier. Just write:

Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")
 
B

Barry

Made that change, and tried running again...it almost completed, with error
"Run-time error '-2147467259 (80004005)': Automation error Unspecified", at
line: gp.CopyRows_management Well_test, XYWells, "".
 
D

Dirk Goldgar

Barry said:
Made that change, and tried running again...it almost completed, with
error
"Run-time error '-2147467259 (80004005)': Automation error Unspecified",
at
line: gp.CopyRows_management Well_test, XYWells, "".
Click to expand...


I'm afraid that's outside my competence. I don't know anything about the
mapping object you're using or the methods it exports.
 
B

Barry

I found in the Help document "Command Object (ADO)":

"To execute a Command, simply call it by its Name property on the associated
Connection object. The Command must have its ActiveConnection property set to
the Connection object. If the Command has parameters, pass their values as
arguments to the method."

Is this an alternative to pursue?
 
D

David W. Fenton

I'm afraid that's outside my competence. I don't know anything
about the mapping object you're using or the methods it exports.
Click to expand...

Your VBScript uses late binding, which is good.

In Access, you have the choice of using Early Binding by creating a
reference to the relevant library. You do this from the Debug menu
of the VBE window. Once you've created a reference, you can use the
object browser (hit F2 while in the VBE) to see what the properties
and methods of your referenced mapping library are. This could help
clear up what's going wrong.
 
B

Barry

Got it, with much help from Bob Barrows, in the data.ado discussion group,
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&lang=en&cr=US.

May also run the VBScript version of it from Windows (and schedule the task).

Thanks to all of you for your help!

Script follows:

Attribute VB_Name = "TabletoFeatureClass"

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 29 2008 10:53:52 AM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------
Sub TabletoFeatureClass()
' Create the Geoprocessor object
Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
SDE_XYWells_end = "Database
Connections\apps1.sde\SDE.test_data\SDE.XYWells_end"
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells_Layer = "Well_test_Layer"
Spatial_Grid_Index = "5471.67632751153"
Spatial_Grid_Index2 = "0"
Spatial_Grid_Index3 = "0"

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management Well_test, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.OverwriteOutput = 1
gp.CopyFeatures_management XYWells_Layer, SDE_XYWells_end, "", "0", "0", "0"

' Process: Calculate Default Spatial Grid Index...
gp.CalculateDefaultGridIndex_management SDE_XYWells_end

End Sub
 
B

Barry

Got it, with much help from Bob Barrows, in the data.ado discussion group,
http://msdn.microsoft.com/newsgroups/default.aspx?dg=microsoft.public.data.ado&lang=en&cr=US.

Thanks to you all for your help!

Script follows:

Attribute VB_Name = "TabletoFeatureClass"

' ---------------------------------------------------------------------------
' TabletoFeatureClass.vbs
' Created on: Tue Jan 29 2008 10:53:52 AM
' (generated by ArcGIS/ModelBuilder)
' ---------------------------------------------------------------------------
Sub TabletoFeatureClass()
' Create the Geoprocessor object
Set gp = CreateObject("esriGeoprocessing.GPDispatch.1")

' Load required toolboxes...
gp.AddToolbox "D:/Program Files/ArcGIS/ArcToolbox/Toolboxes/Data Management
Tools.tbx"


' Local variables...
SDE_XYWells_end = "Database
Connections\apps1.sde\SDE.test_data\SDE.XYWells_end"
Well_test = "D:\test\Well_Annotation.mdb\Well_test"
XYWells_Layer = "Well_test_Layer"
Spatial_Grid_Index = "5471.67632751153"
Spatial_Grid_Index2 = "0"
Spatial_Grid_Index3 = "0"

' Process: Make XY Event Layer...
gp.MakeXYEventLayer_management Well_test, "EASTING", "NORTHING",
XYWells_Layer,
"PROJCS['NAD_1983_StatePlane_Texas_North_FIPS_4201_Feet',GEOGCS['GCS_North_American_1983',DATUM['D_North_American_1983',SPHEROID['GRS_1980',6378137.0,298.257222101]],PRIMEM['Greenwich',0.0],UNIT['Degree',0.0174532925199433]],PROJECTION['Lambert_Conformal_Conic'],PARAMETER['False_Easting',656166.6666666665],PARAMETER['False_Northing',3280833.333333333],PARAMETER['Central_Meridian',-101.5],PARAMETER['Standard_Parallel_1',34.65],PARAMETER['Standard_Parallel_2',36.18333333333333],PARAMETER['Latitude_Of_Origin',34.0],UNIT['Foot_US',0.3048006096012192]];IsHighPrecision"

' Process: Copy Features...
gp.OverwriteOutput = 1
gp.CopyFeatures_management XYWells_Layer, SDE_XYWells_end, "", "0", "0", "0"

' Process: Calculate Default Spatial Grid Index...
gp.CalculateDefaultGridIndex_management SDE_XYWells_end

End Sub
 
Top