filedialog vs Common dialog

T

tmaxwell

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.Is it
Common Dialog in 2000? 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
 
A

Amy Blankenship

In the VBA window, go to Tools>References and browse for the oldest version
of the Microsoft Office library you can find on your system. For instance
C:\Program files\Microsoft Office\Office 10\MSACC.OLB.

HTH;

Amy
 
G

George Nicholson

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*.
 
T

tmaxwell

Doug,

If you look at the script below, I need to import the NAMEADDR.txt into

'Create empty Table for imported data
DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"
When I went to the recommended site I tried to include the code but, I then
I get a CopyObject error. Is there not an easier way of just setting the path
of the file NAMEADDR. It was suggested that I could just use......
strTextFile = "\\Bgnhss2kfs01\Reports\Credit\Text Files\NAMEADDR.txt" I
know you will need more info, but I figured to start here.Thanks
 
D

Douglas J. Steele

I'm sorry, I don't understand what you're trying to do.

With what did you replace the following code:

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

With dlgOpen

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

End With 'dlgOpen
 
T

tmaxwell

The web page stated I could use the following. It worked I pick the
NAMEADDR.txt file, but then a get a copyobject error into the NewName:=
ImportTable

Dim strFilter As String
Dim strInputFileName As String


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

tmaxwell

In 2003 there is the dlgOpen as FileDialog, but the company is using 2000. It
is built in 2003. I just need to set the file path to NAMEADDR. The code
that is failing is below. CopyObject Canceled is the error. I need to import
the text file into a template table to update when they need to re-run the
query.


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

tmaxwell

I sorrry the exact line is SourceObjectName:="NameAddr_Template" out of
the create empty table for imported data!
 
D

Douglas J. Steele

That DoCmd.CopyObject code has absolutely nothing to do with using either
FileDialog or the API approach to picking a file, so I'm confused as to what
you're trying to do.
 
T

tmaxwell

Let me go back a bit, I have a DB that took my main table NameAddr, this
linked file contains all of our customers with assigned customer numbers.

Each of our 14 branches has a range of usage between 1 and 9999. Branch 01
only uses 1680 that leaves 8319 number assignable. I created a 1-9999 table
and right joined them to NAMEADDR.txt, this did give me all assigned number,
but not unassigned.

So the queries below was suggested to give me the results I need. The
NAMEADDR.txt is a linked file, this is because I use the DB for another
reason. I also have a table “just to import†the NAMEADDR. txt , These Q
below uses VBscripting that has a Import table to import a text file
invoking them from a Switchboard form. I have Office 2000 that does not have
FileDilaog to open the file dialog box. The DB was created in 2003 that has
Filedialog. When I run the Macro to import the new data I get the
User-defined not defined error when I run it.


Q_9999
SELECT Q_9999_Basis.Number
FROM Q_9999_Basis
WHERE (((Q_9999_Basis.Number)>0))
ORDER BY Q_9999_Basis.Number;


Q_Branches
SELECT DISTINCT NameAddrImported.[co-number]
FROM NameAddrImported
ORDER BY NameAddrImported.[co-number];

Q_Combo
SELECT Q_Branches.[co-number] AS Branch, Q_9999.Number
FROM Q_9999, Q_Branches
ORDER BY Q_Branches.[co-number], Q_9999.Number;



Q_Unused
SELECT Q_Combo.Branch, Q_Combo.Number
FROM Q_Combo LEFT JOIN NameAddrImported ON (Q_Combo.Number =
NameAddrImported.[cust-number]) AND (Q_Combo.Branch =
NameAddrImported.[co-number])
WHERE (((NameAddrImported.[co-number]) Is Null))
ORDER BY Q_Combo.Branch, Q_Combo.Number;


Below is the VB that’s 2003 and I will post the work around that was at a
suggested web site. I am not sure if I included the code correctly.


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
Dim strFilter As String
Dim strInputFileName As String





'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 = "Bgnhss2kfs01 \ Reports \ Credit \ TextFiles \
NAMEADDR.txt"

'End With 'dlgOpen


strFilter = ahtAddFilterItem(strFilter, "text files (*.txt)", "*.txt")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_READONLY)




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


I added this coding to the script it worked at first. A prompt dialog box
opened, asked what file to choose, I pick NAMEADDR, but it was at this point
I got the error CopyObject canceled, in the Create empty table for imported
data. Again I am not sure I included the API code correctly?

strFilter = ahtAddFilterItem(strFilter, "text files (*.txt)", "*.txt")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_READONLY)


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

Douglas J. Steele

That doesn't clear things up at all, at least not for me.

You're getting the Window File Open dialog and selecting a file using it,
correct?

Then, you're issuing the statement

DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"

What that does is create a new table (named "NameAddrImported") in your
database, by copying an existing table in your database (named
"NameAddr_Template")

Do you already have a table named NameAddrImported in your database? Does
the table NameAddr_Template exist in your database? The only reason the copy
should fail is if the table already exists, or the template doesn't exist.

I still don't see any connection between the File Open dialog and your copy
statement.

However, I'm assuming that what you want is to select a file using the File
Open dialog and use that file in your code that reads from a file.

If that's so, then your code is retrieving the path to the file you selected
as strInputFileName

That means that you need to change

Set txsTextFile = fsTextFile.OpenTextFile( _
strTextFile, ForReading)

to

Set txsTextFile = fsTextFile.OpenTextFile( _
strInputFileName, ForReading)


--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


tmaxwell said:
Let me go back a bit, I have a DB that took my main table NameAddr, this
linked file contains all of our customers with assigned customer numbers.

Each of our 14 branches has a range of usage between 1 and 9999. Branch 01
only uses 1680 that leaves 8319 number assignable. I created a 1-9999
table
and right joined them to NAMEADDR.txt, this did give me all assigned
number,
but not unassigned.

So the queries below was suggested to give me the results I need. The
NAMEADDR.txt is a linked file, this is because I use the DB for another
reason. I also have a table "just to import" the NAMEADDR. txt , These Q
below uses VBscripting that has a Import table to import a text file
invoking them from a Switchboard form. I have Office 2000 that does not
have
FileDilaog to open the file dialog box. The DB was created in 2003 that
has
Filedialog. When I run the Macro to import the new data I get the
User-defined not defined error when I run it.
Click to expand...
<snip>
 
T

tmaxwell

Doug,
I have a table named "NameAddrImported" and also a table named
NameAddr_template. It's a Macro that uses VBscripting that has a Import table
to import a text file invoking them from a Switchboard form. I have Office
2000 that does not have FileDialogOpen to set the path. Then copy the
NAMEADDR.txt into the NameAddrImported. Should I remove the table from the DB
so it can create each time the marco is ran?

DoCmd.CopyObject NewName:="NameAddrImported", _
SourceObjectType:=acTable, _
SourceObjectName:="NameAddr_Template"
 
D

Douglas J. Steele

AFAIK, the CopyObject method assumes that the object named as NewName
doesn't exist, so yes, you either need to delete the table, or (better)
simply run a DELETE query to remove all of the data from it.

To read the data from NAMEADDR.txt, make the change I pointed out.

Is there some reason you're not using the TransferText method to import the
data from that file?
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

User defined not defined 3
user define not defined 1
FileDialog 2000 3
User defined error 2
Actual open a file using a Filedialog box 0
Actual open a file using a Filedialog box 2
Form Losing Focus 0
File dialog box 1

Top