I have a DB that uses VBscripting that has a Import table to import a text
file invoking them from a Switchboard form. I get the User-defined not
defined when I run it. This is the Dim dlgOpen As FileDialog ( 'Open-file
dialog box) It was built in Office 2003, I can not find it in Office 2000. I
will list the VB Script hoping someone can tell me where to find. I've looked
in MS Scripting Runtime, MOOL, Shell controls?
Thanks Todd

Option Compare Database
Option Explicit


Public Function ImportText()

Dim varFields As Variant 'Fields found in text record
Dim strInputLine As String 'Line read from file
Dim intLineNum As Integer 'Line number within logical record
Dim rstNewTable As DAO.Recordset 'Table to contain imported records

Dim dlgOpen As FileDialog 'Open-file dialog box

Dim fsTextFile As New Scripting.FileSystemObject 'Allows appending
Dim strTextFile As String 'Name of file to be imported
Dim txsTextFile As Scripting.TextStream 'Allows access to files

'Create empty Table for imported data
DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"

'Display the "Open File" dialog window
Set dlgOpen = Application.FileDialog( _
DialogType:=msoFileDialogOpen)

With dlgOpen

.AllowMultiSelect = False
.Show
strTextFile = .SelectedItems(1)

End With 'dlgOpen

Set txsTextFile = fsTextFile.OpenTextFile( _
strTextFile, ForReading)
Set rstNewTable = CurrentDb.OpenRecordset( _
"NameAddrImported", dbOpenTable, dbAppendOnly)

'Begin reading records

Do While Not txsTextFile.AtEndOfStream
strInputLine = ""
For intLineNum = 1 To 3
If txsTextFile.AtEndOfStream Then Exit Do
strInputLine = strInputLine & txsTextFile.ReadLine
Next intLineNum
varFields = Split(strInputLine, "~")
With rstNewTable

.AddNew
.Fields("co-number") = Trim$(varFields(0))

'Warning: Some of these customer account numbers
' contain way more than 4 digits,
' but we convert only the last 4 digits.
' This is likely to give rise to duplicate values.
.Fields("cust-number") = _
CLng(Val(Right$(Trim$(varFields(1)), 4)))

.Fields("cust-name") = Trim$(varFields(2))
.Fields("address 1") = Trim$(varFields(3))
.Fields("address 2") = Trim$(varFields(4))
.Fields("city") = Trim$(varFields(5))
.Fields("state") = Trim$(varFields(6))
.Fields("zip") = Trim$(varFields(7))
.Fields("phone-number") = Trim$(varFields(8))
.Fields("ca-number") = Trim$(varFields(9))
.Fields("cm-number") = Trim$(varFields(10))

.Update 'Append this record to the Table

End With 'rstNewTable

Loop 'While Not txsTextFile...

txsTextFile.Close

'Now release any objects invoked for this procedure.
If Not (rstNewTable Is Nothing) Then Set rstNewTable = Nothing
If Not (dlgOpen Is Nothing) Then Set dlgOpen = Nothing
If Not (fsTextFile Is Nothing) Then Set fsTextFile = Nothing
If Not (txsTextFile Is Nothing) Then Set txsTextFile = Nothing

MsgBox "Text has been imported from " & strTextFile & " ."

End Function 'ImportText

Was this post helpful to you?

Why should I rate a post?

You might want to try the Microsoft Access Discussion Groups.
---
Gary

"tmaxwell" wrote:

I have a DB that uses VBscripting that has a Import table to import a text
file invoking them from a Switchboard form. I get the User-defined not
defined when I run it. This is the Dim dlgOpen As FileDialog ( 'Open-file
dialog box) It was built in Office 2003, I can not find it in Office 2000. I
will list the VB Script hoping someone can tell me where to find. I've looked
in MS Scripting Runtime, MOOL, Shell controls?
Thanks Todd

Option Compare Database
Option Explicit


Public Function ImportText()

Dim varFields As Variant 'Fields found in text record
Dim strInputLine As String 'Line read from file
Dim intLineNum As Integer 'Line number within logical record
Dim rstNewTable As DAO.Recordset 'Table to contain imported records

Dim dlgOpen As FileDialog 'Open-file dialog box

Dim fsTextFile As New Scripting.FileSystemObject 'Allows appending
Dim strTextFile As String 'Name of file to be imported
Dim txsTextFile As Scripting.TextStream 'Allows access to files

'Create empty Table for imported data
DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"

'Display the "Open File" dialog window
Set dlgOpen = Application.FileDialog( _
DialogType:=msoFileDialogOpen)

With dlgOpen

.AllowMultiSelect = False
.Show
strTextFile = .SelectedItems(1)

End With 'dlgOpen

Set txsTextFile = fsTextFile.OpenTextFile( _
strTextFile, ForReading)
Set rstNewTable = CurrentDb.OpenRecordset( _
"NameAddrImported", dbOpenTable, dbAppendOnly)

'Begin reading records

Do While Not txsTextFile.AtEndOfStream
strInputLine = ""
For intLineNum = 1 To 3
If txsTextFile.AtEndOfStream Then Exit Do
strInputLine = strInputLine & txsTextFile.ReadLine
Next intLineNum
varFields = Split(strInputLine, "~")
With rstNewTable

.AddNew
.Fields("co-number") = Trim$(varFields(0))

'Warning: Some of these customer account numbers
' contain way more than 4 digits,
' but we convert only the last 4 digits.
' This is likely to give rise to duplicate values.
.Fields("cust-number") = _
CLng(Val(Right$(Trim$(varFields(1)), 4)))

.Fields("cust-name") = Trim$(varFields(2))
.Fields("address 1") = Trim$(varFields(3))
.Fields("address 2") = Trim$(varFields(4))
.Fields("city") = Trim$(varFields(5))
.Fields("state") = Trim$(varFields(6))
.Fields("zip") = Trim$(varFields(7))
.Fields("phone-number") = Trim$(varFields(8))
.Fields("ca-number") = Trim$(varFields(9))
.Fields("cm-number") = Trim$(varFields(10))

.Update 'Append this record to the Table

End With 'rstNewTable

Loop 'While Not txsTextFile...

txsTextFile.Close

'Now release any objects invoked for this procedure.
If Not (rstNewTable Is Nothing) Then Set rstNewTable = Nothing
If Not (dlgOpen Is Nothing) Then Set dlgOpen = Nothing
If Not (fsTextFile Is Nothing) Then Set fsTextFile = Nothing
If Not (txsTextFile Is Nothing) Then Set txsTextFile = Nothing

MsgBox "Text has been imported from " & strTextFile & " ."

End Function 'ImportText

Was this post helpful to you?

Why should I rate a post?

1 response in Microsoft.public.access macros, Message: "User defined not
defined"

Never multi-post (identical messages sent separately to different groups).
Crossposting (the same message with multiple newsgroups in the "To:" line)
is ok, *within reason*.

--
George Nicholson

Remove 'Junk' from return address.


"tmaxwell" wrote in message
...
I have a DB that uses VBscripting that has a Import table to import a text
file invoking them from a Switchboard form. I get the User-defined not
defined when I run it. This is the Dim dlgOpen As FileDialog (
'Open-file
dialog box) It was built in Office 2003, I can not find it in Office
2000. I
will list the VB Script hoping someone can tell me where to find. I've
looked
in MS Scripting Runtime, MOOL, Shell controls?
Thanks Todd

Option Compare Database
Option Explicit


Public Function ImportText()

Dim varFields As Variant 'Fields found in text record
Dim strInputLine As String 'Line read from file
Dim intLineNum As Integer 'Line number within logical record
Dim rstNewTable As DAO.Recordset 'Table to contain imported records

Dim dlgOpen As FileDialog 'Open-file dialog box

Dim fsTextFile As New Scripting.FileSystemObject 'Allows appending
Dim strTextFile As String 'Name of file to be imported
Dim txsTextFile As Scripting.TextStream 'Allows access to files

'Create empty Table for imported data
DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"

'Display the "Open File" dialog window
Set dlgOpen = Application.FileDialog( _
DialogType:=msoFileDialogOpen)

With dlgOpen

.AllowMultiSelect = False
.Show
strTextFile = .SelectedItems(1)

End With 'dlgOpen

Set txsTextFile = fsTextFile.OpenTextFile( _
strTextFile, ForReading)
Set rstNewTable = CurrentDb.OpenRecordset( _
"NameAddrImported", dbOpenTable, dbAppendOnly)

'Begin reading records

Do While Not txsTextFile.AtEndOfStream
strInputLine = ""
For intLineNum = 1 To 3
If txsTextFile.AtEndOfStream Then Exit Do
strInputLine = strInputLine & txsTextFile.ReadLine
Next intLineNum
varFields = Split(strInputLine, "~")
With rstNewTable

.AddNew
.Fields("co-number") = Trim$(varFields(0))

'Warning: Some of these customer account numbers
' contain way more than 4 digits,
' but we convert only the last 4 digits.
' This is likely to give rise to duplicate values.
.Fields("cust-number") = _
CLng(Val(Right$(Trim$(varFields(1)), 4)))

.Fields("cust-name") = Trim$(varFields(2))
.Fields("address 1") = Trim$(varFields(3))
.Fields("address 2") = Trim$(varFields(4))
.Fields("city") = Trim$(varFields(5))
.Fields("state") = Trim$(varFields(6))
.Fields("zip") = Trim$(varFields(7))
.Fields("phone-number") = Trim$(varFields(8))
.Fields("ca-number") = Trim$(varFields(9))
.Fields("cm-number") = Trim$(varFields(10))

.Update 'Append this record to the Table

End With 'rstNewTable

Loop 'While Not txsTextFile...

txsTextFile.Close

'Now release any objects invoked for this procedure.
If Not (rstNewTable Is Nothing) Then Set rstNewTable = Nothing
If Not (dlgOpen Is Nothing) Then Set dlgOpen = Nothing
If Not (fsTextFile Is Nothing) Then Set fsTextFile = Nothing
If Not (txsTextFile Is Nothing) Then Set txsTextFile = Nothing

MsgBox "Text has been imported from " & strTextFile & " ."

End Function 'ImportText

Was this post helpful to you?

Why should I rate a post?