Hi RuralGuy everything worked out great, sorry I didn't let you know sooner, I can now finish my app, all thanks to you.
Hi RuralGuy everything worked out great, sorry I didn't let you know sooner, I can now finish my app, all thanks to you.
That's great! Are you ready to use the "Solved" thread tool?
Talk about a time machine. Did you single step the code? Is it importing anything or does it hang up on the 1st attempt.
Haha yes :-), this article is still useful! Here is your code, and it gives me the error on the first attempt.
Public Sub NIPSImport()
'-- Import all of the NIPS raw data to the NIPS table.
Dim OurFolder As String
Dim OurFile As String
'-- First have the user select the folder
OurFolder = BrowseFolder1("Select the folder that contains the NIPS Text Files.")
'-- Dir returns a ZLS ("") when no more files match the criteria
OurFile = Dir(OurFolder & "\*.txt")
Do While OurFile <> ""
DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", OurFile
'-- Get the next *.txt filename
OurFile = Dir
Loop
End Sub
However, this code doesn't error out when I manually import a single file using the text import wizard, from there somehow it recognizes the object name and then your code works on the entire directory. Any ideas? Thanks.
Is there a nips_030720 12.txt file and does it have an embedded space in the name? Does the NIPS table already exist?
It sounds like you have done everything correctly. I'm sorry but I don't know what could be causing the problem at this time.
Yeah I know, it all looks good... Thanks again!
Thanks Allan. This code will save me some time. I've been wanting to have an option like this for a while, but haven't taken the time to search/ write my own code.
jeraldc
I just ran the code after a little modification:
I set a break point at the lineCode:MsgBox "found file > " & OurFile ' DoCmd.TransferText acImportDelim, "ScoreImportSpecs", "tblScores", OurFile
OurFolder = BrowseFolder1("Select the folder that contains the scores.")
It stepped through without error.
Which line does the error occur?
When single stepping (SS) through the code, watch the "local window" pane in the IDE. Does the data in the variables look correct?
Care to share the mdb, with just the form, table and a couple of the text files? Munge the sensitive data in the text files or add test data.
The error occurs in this line.
DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", OurFile
Access is looking for the object 'nips.txt' filename ... here it is. Nothing personal or sensitive in it since it's public information.DRAFT - NIPS-STAMPS Recon v01.mdbnips_02012012.txtnips_03132012.txtnips_03152012.txt
So I had to open the mdb in A2K7, then convert it to A2K.
When you open the mdb, open the IDE and check the references. Since I have A2k, and you have A2K3, there will probably have a missing reference.
OK, things I found:
You were missing the global declarations.
You cannot name a module the same name as a function/sub. I put both import functions in the same module and renamed it "ImportFunctions" (no spaces) .
You should always have "Option Explicit" at the top of every code page (I added it). There is a setting that adds the line to any new code page.
------------
I modified the code from Allan a little to report the file being processed - the file name is displayed in the status bar area.
And I modified the delete routine. You are actually clearing the tables, not the database. Picky, picky.....I know
So it seems to work for me......
How about you??
Hey ssanfu,
Thanks a lot! I didn't get an automated response from the server on your comment so I was going out solving it myself (wish I saw your response here first, but I was able to enhance my code a bit and make it work!)
This code works beautifully below! I found out (you are right as well) that the "\*.txt" was not identifying an object for the text file in the code OurFile = Dir(OurFolder & "\*.txt"). So I created and reference the object name below.
Public Sub NIPSImport()
On Error GoTo bImportFiles_Click_Err
Dim objFS As Object, objFolder As Object
Dim objFiles As Object, objF1 As Object
Dim strFolderPath As String
Dim OurFolder As String
OurFolder = BrowseFolder1("Select the folder that contains the NIPS Text Files.")
strFolderPath = OurFolder & "\"
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strFolderPath)
Set objFiles = objFolder.files
For Each objF1 In objFiles
If Right(objF1.Name, 3) = "txt" Then
DoCmd.TransferText acImportDelim, "ImportNIPS", "NIPS", strFolderPath & objF1.Name, False
'Check if archive folder exists, if not create one
If Dir(OurFolder & "\Archive", vbDirectory) = "" Then
MkDir (OurFolder & "\Archive")
Else
'Do nothing if the directory already exists
'MsgBox "Archive directory already exists"
End If
'Move the files to the archive folder
Name strFolderPath & objF1.Name As OurFolder & "\Archive\" & objF1.Name
End If
Next
Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
bImportFiles_Click_Exit:
Exit Sub
bImportFiles_Click_Err:
MsgBox Err.Number & " " & Err.Description
Resume bImportFiles_Click_Exit
End Sub
Hello RuralGuy,
I am new to this forum and to Access 2010. I have used Ascess before but an older version.
This code works Great. I have one question. Is there a way to eliminate the import screen telling how many records were imported and not imported?
Thanks
Ralph
Hello RuralGuy,Put the following code in a standard module name basImport.
Then call GetScores from the Click event of a CommandButton.Code:Option Compare Database Option Explicit '************** Code Start ************** 'This code was originally written by Terry Kreft. 'It is not to be altered or distributed, 'except as part of an application. 'You are free to use it in any application, 'provided the copyright notice is left unchanged. ' 'Code courtesy of 'Terry Kreft Private Type BROWSEINFO hOwner As Long pidlRoot As Long pszDisplayName As String lpszTitle As String ulFlags As Long lpfn As Long lParam As Long iImage As Long End Type Private Declare Function SHGetPathFromIDList Lib "shell32.dll" Alias _ "SHGetPathFromIDListA" (ByVal pidl As Long, _ ByVal pszPath As String) As Long Private Declare Function SHBrowseForFolder Lib "shell32.dll" Alias _ "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) _ As Long Private Const BIF_RETURNONLYFSDIRS = &H1 Public Function BrowseFolder1(szDialogTitle As String) As String Dim X As Long, bi As BROWSEINFO, dwIList As Long Dim szPath As String, wPos As Integer With bi .hOwner = hWndAccessApp .lpszTitle = szDialogTitle .ulFlags = BIF_RETURNONLYFSDIRS End With dwIList = SHBrowseForFolder(bi) szPath = Space$(512) X = SHGetPathFromIDList(ByVal dwIList, ByVal szPath) If X Then wPos = InStr(szPath, Chr(0)) BrowseFolder1 = left$(szPath, wPos - 1) Else BrowseFolder1 = vbNullString End If End Function '*********** Code End ***************** Public Sub GetScores() '-- Import all of the student scores to the tblScores table. Dim OurFolder As String Dim OurFile As String '-- First have the user select the folder OurFolder = BrowseFolder1("Select the folder that contains the scores.") '-- Dir returns a ZLS ("") when no more files match the criteria OurFile = Dir(OurFolder & "\*.txt") Do While OurFile <> "" '-- I created and saved the ScoreImportSpecs earlier. '-- I also created the tblScores table with the following '-- four text fields: '-- FirstName, LastName, ClassCode, Score DoCmd.TransferText acImportDelim, "ScoreImportSpecs", "tblScores", OurFile '-- Get the next *.txt filename OurFile = Dir Loop End Sub
You will need to create the tblScores first and the Import Specification and save it as ScoreImportSpecs.
I am new to this forum and to Access 2010. I have used Ascess before but an older version.
This code works Great. I have one question. Is there a way to eliminate the import screen telling how many records were imported and not imported?
Sorry if I am messing up the post.
Thanks
Ralph