TransferText to make batch file, need help

[Marian M.]

I need to create a batch file with ~6000 lines in it for ArcGIS. Python is
beyond me, so I'm trying to do this with VBA in an Access module using hints
I've found through this discussion group (VBA is what I know how to use).
Each line should be:

Intersect_analysis "C:\FICE93_25\strID.shp #;'C:\LSAY\Census\SHPs\1990\1990
Block Groups\BlkGrp90_Albers.shp' #" C:\FICE93_25\strID_Intersect.shp ALL #
INPUT

I've broken the line into 4 parts, strStart, strMid, strEnd & strID, using
the code at the end of this message. The problem is, I get "Compile error:
Expected: end of statement" for strStart & strMid. MS VBA doesn't seem to
like the 'C:' in "C:\FICE93_25\". What am I doing wrong? (since I can't get
beyond those two lines, I don't know how well the rest of the code works)

TIA,
Marian

the code:
--------------
Sub MyBatchFile()

Dim strStart As String, strMid As String, strEnd As String, strID As String
Dim iLoop As Long
Dim dbs As Database, rsID As Recordset

Set dbs = CurrentDb
' Set the path to the directory where the files will be.
For iLoop = 1 To 5945
Set rsID = dbs.OpenRecordset("Select FICE93_ID From AdYrLists Where
FICE93_ID = " & iLoop)
strID = rsID!FICE93_ID ' Set the first state string
strStart = "Intersect_analysis " & """ & "C:\FICE93_25\"
strMid = ".shp #;'C:\LSAY\Census\SHPs\1990\1990 Block
Groups\BlkGrp90_Albers.shp' #" & " "" & "C:\FICE93_25\"
strEnd = "_Intersect.shp ALL # INPUT"
strSQL = strStart & strID & strMid & strID & strEnd
strQ = "BatchLine"
Set qdf = dbs.CreateQueryDef(strQ, strSQL)
qdf.Close
DoCmd.TransferText acExportDelim, "AdYrLists ExSpec", qdf.Name,
"C:\LSAY\MigrPath\FICE93_25.txt"
dbs.QueryDefs.Delete strQ
Set qdf = Nothing
Next iLoop

End Sub
------------

 

[Steve Sanford]

Hi Marian,

The problem with those two lines are double quotes. Since it is a text
string, you could use

strStart = "Intersect_analysis C:\FICE93_25\"

' this should be on one line
strMid = ".shp #;'C:\LSAY\Census\SHPs\1990\1990 Block
Groups\BlkGrp90_Albers.shp' # C:\FICE93_25\"


But looking at the sub, it looks like you are creating a text file with
lines that look like this:

Intersect_analysis C:\FICE93_25\1.shp #;'C:\LSAY........
Intersect_analysis C:\FICE93_25\2.shp #;'C:\LSAY........
Intersect_analysis C:\FICE93_25\3.shp #;'C:\LSAY........
..
..
Intersect_analysis C:\FICE93_25\5945.shp #;'C:\LSAY......


If this is right, you can use VBA to write the text file directly.
Try this code:

(Watch for line wrap)
'--------- end code ------------------
Sub MyBatchFile2Txt()

Dim WF As Integer
Dim FileToWrite As String
Dim strStart As String, strMid As String, strEnd As String, strID As
String,
Dim strSql As String
Dim iLoop As Long

'get the first free filehandle
WF = FreeFile

'Where to put the file
FileToWrite = "FICE93_25.txt"

Open FileToWrite For Output As #WF

For iLoop = 1 To 5945
strStart = "Intersect_analysis C:\FICE93_25\"

strMid = ".shp #;'C:\LSAY\Census\SHPs\1990\1990 Block
Groups\BlkGrp90_Albers.shp' # C:\FICE93_25\"

strEnd = "_Intersect.shp ALL # INPUT"
strSql = strStart & iLoop & strMid & strID & strEnd

'use this line to enclose the string in quotes
' Write #WF, strSql

'this line does not enclose the string quotes
Print #WF, strSql

Next iLoop

Close #WF
MsgBox "DONE!"

End Sub
'--------- end code ------------------


If this is more than a one time thing, you could create a form that has two
text boxes for the beginning number and the ending number and use the
standard Windows File Open/Save dialog box to change the file name and pick
where it is saved.
The code would be in a buttons's click event.


If I misunderstood what you wanted, please post back...

HTH

 

[Andrew Thompson]

(Watch for line wrap)

Why 'watch', when there are tools specifically designed
to *check* line width? Here is one such tool.
<http://www.physci.org/twc.jnlp>

 

[Marian M.]

Thank you Steve. Your code worked a treat. I'll have to admit, I'm much more
of a backwards engineer than a coder.

I do still have one question. What is the significance of the '#' in '#WF'?
I haven't been able to make the Access-VBA help site cough up an answer.

Marian

 

[Douglas J. Steele]

WF is an integer value set to represent a handle for a file in the statement
"WF = FreeFile"

The VBA syntax for most of the file I/O functions require you to prefex the
handle number with the # character.

Open FileToWrite For Output As #WF
Write #WF, strSql
Close #WF

 

[Steve Sanford]

What Doug said.........