User defined not defined

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
Sorry about that, I did not realize it went out to the same groups. I am
working with Office 2003, some people have 2000. I built this on 2003, but is
this the open file dialog for 2000.

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

Instead of this?

Dim dlgOpen As FileDialog 'Open-file dialog box
 
Once you've copied all of the code from that web page into a module, then
yes, the 4 lines of code you indicate will open the standard Windows File
Open dialog, letting your users select Excel files.
 
Back
Top