import text file

[catt]

Hi:

I am finally trying my hand at modules and VBA. I have converted some of my macros into modules and now would like to streamline some of the commands.

One of the lines of the module imports a text file into a table.

Dim FALL01 As String
Dim SPRING02 As String


FALL01 = "C:\SQR\FALL01.TXT"
SPRING02 = "C:\SQR\SPR02.TXT"

DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", FALL01, False, ""

Where FALL01 is the path per above.

What I would like to do is for the user to choose which semester they would like to import.

IF [FORM].[F-TEST FUNCTION].[SEMESTER]= FALL01

THEN
DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", FALL01, False, ""

Else

DoCmd.TransferText acImportFixed, "FALL01 Import Specification", "CHOOSE-S", SPRING02, False, ""

End If


This is from a form's unbound textbox. Here the user can enter the file for the semester they need imported. But I get a syntax error when I try to run this procedure.

Can anyone help?

Thank You

Judy

 

[Drevlin]

If and Then need to be on the same line.

You have:
IF [FORM].[F-TEST FUNCTION].[SEMESTER]= FALL01

THEN

But it should be

If [FORM].[F-TEST FUNCTION].[SEMESTER]= FALL01 Then


Are you expecting the User to be typing in:

C:\SQR\FALL01.TXT

into the textbox?

That would be the only way this particular peice of code would work. If the user is actually typing "FALL01" into the textbox then you would need to put it in quotes in your code.

If [FORM].[F-TEST FUNCTION].[SEMESTER]= "FALL01" Then

A combobox or listbox would probably be a better choice that way the user can't accidentally type in the wrong thing.

 

[catt]

Thank You

It would have taken me a while to figure out that "Then" was at the wrong line.


Judy

 

[samuriacornflak]

Is there way to develop a button, or command that will prompt a windows explorer screen or some sort of search inorder to find the txt files you would like to import??? any help would be greatly appricated

 

[CATT1]

Open Explorer Window

I don’t understand exactly what you mean. Did you mean you want to open a window where you can choose what file you want to import? This would mean you do not know the name of the file or the extension? I am sorry I haven’t figured out how to just open a window, but I have been able to import any file with a particular extension, like all .dat files or all .rec files through a loop. If I need one file out of several choices, I created case statements.

If you need help with any of these, let me know.


Judy

 

[modest]

Is there way to develop a button, or command that will prompt a windows explorer screen or some sort of search inorder to find the txt files you would like to import??? any help would be greatly appricated

Use the forum's search function and look for Find File, Get File, or Open File

 

[ghudson]

Try searching for the keyword ... Browse for a quick listing of 500+ threads related to the subject.

 

[modest]

501+ =)

 

[samuriacornflak]

Thanks alot guys for the help...Ghudson, I downloaded your browse.zip and was trying to get it to work, but everytime I hit import it tells me that that "my computer does not have a c:\windows\win.ini file so the import example will not work"...is there any way to remedy this...I kind of new to all this and have the basic concept, but still need a more practice and knowledge, and want to thank people like you who help dumb@ss like myself

 

[modest]

if you have Win2k it will be in C:\winnt\

 

[samuriacornflak]

so just replace the c:\windows\win.ini with c:\winnt\win.ini and it should work??

 

[ghudson]

Replace the Private Sub bImport_Click() sub with this in my browsing sample...

Private Sub bImport_Click()
On Error GoTo Err_bImport_Click

    Me.tbHidden.SetFocus
    
    Dim sWindows
    sWindows = Environ("WinDir") & "\"
    
    If IsNull(tbFile) Or tbFile = "" Then
        MsgBox "Please browse and select a valid file to import.", vbCritical, "Invalid File"
    Else
        If Dir(sWindows & "Win.ini") <> "" Then
            CurrentDb().Execute "DELETE * FROM tImport"
            FileCopy sWindows & "Win.ini", sWindows & "Win.txt"
            DoCmd.TransferText acImportDelim, , "tImport", sWindows & "Win.txt", False
            MsgBox "For testing purposes the 'Import' button has imported your C:\Windows\Win.ini file into the tImport table."
        Else
            MsgBox "Your computer does not have a " & sWindows & "Win.ini file so the import example will not work.", vbInformation
        End If
    End If
    
Exit_bImport_Click:
    Exit Sub

Err_bImport_Click:
    MsgBox Err.Number & " - " & Err.Description
    Resume Exit_bImport_Click

End Sub

That should find the Win.ini file no matter what version of Windows you are using. Please post back if this work for you and also which version of Windows you are using. Thanks!

 

[samuriacornflak]

it gives me the error message "3078- the microsoft jet database engine cannot find the input table or query 'timport'. Make sure it exsits and that its name is spelled correctly" I am running this on access 97' and my os is windows 2000 proffesional

 

[ghudson]

Are you running the browse [import] functions in your database or from in my Browsing sample db?

If it is from your db then yuo need to change the code to import the data into your table or import my tImport table into your do just for the test.

 

[samuriacornflak]

no, this had all been done using your database

 

[ghudson]

I am at a loss since all of the db objects are there in the sample I posted. Is the tImport table not in the tables tab of the db?

 

[samuriacornflak]

I'll tinker around with it....thanks so much for your help