Hello. I have a database which allows a user to import multiple .csv files at one time and adds them to a table. The file name format is based on the date and time including seconds which eliminates the possibility of duplicates since one person creates these throughout the day. Each file is considered a "batch" and can contain anywhere from 1 to 50 (or more) line items. If I can capture the file name and import that into the table that will allow me to show how many "batches" have been submitted.
Is there code that is compatible with what I am currently doing that will also import the filename into the table?
Operating System: Windows XP Professional
Program: Microsoft Access 2003
Example of .csv File Name: AROPS20111104145057.csv (AROPSYYYYMMDDHHMMSS.csv)
Here is the code I am currently using and it works wonderfully:
Dim fDialog As Office.FileDialog
Dim varFile As Variant
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
.AllowMultiSelect = True
.Title = "Choose Files to Import"
.Filters.Add "Text Files", "*.csv"
If .Show = True Then
For Each varFile In .SelectedItems
DoCmd.TransferText acImportDelim, "AROPSImport", "tbl_AROPSImport", varFile
MsgBox "You have cancelled the import."
Any help you can provide would be greatly appreciated. Thank you.
1. Dir() command, scanning & addding file name to table
2. Import Daily File Names to listbox
3. Comparing table to import file
4. Prompt for file name
5. Keep track of file names in a folder
6. Excel Worksheet and File Names in Table
7. Importing multiple uneven sized ranges from .csv file into multiple tables
8. Error when Importing CSV file to Access 2007
9. Transfer Text File Name
10. How to use TransferSpreadseet macro and choose the file name
11. How to get the file name from a dialogue box selection?
12. File Browser to Import Dbase
13. file name to OLE object
14. error handling when importing Excel into Access
15. Report as PDF - setting file name
16. "Output to" - file name to include date
17. Exporting to excel file name
18. Importing Files W/system Date?
19. save folder name to table
I need to scan a directory and add file names in that directory to a table.
I was told that the Dir() command can scan for file names and you can use ADO/DAO code to insert them into your table. But,
I need some guidance on excatly how to do this. Can anyone post some examples on how to do this?
Here is some specific information on what type of data I'm working on:
The file names are in a folder called Box 001
The files are PDF's with a bates number naming convention, such as
open a table
transfer an excel spreadsheet
show all records
save the table
close the table
open a report
It works almost flawlessly but I have two small problems. The table has a column called "startdate". I want the macro to look
at the last entry in the "startdate" column in the table and compare it to the first entry in the "startdate" column of the
import file. If the two "startdate" columns match I want the macro to stop running and present an error similar to, "Start
dates match-check to make sure you have the proper file" or something to that degree. If the "startdate" doesn't match then
continue with the macro. I can't figure out how to tell the macro to compare the import file to the table.
I'm trying to keep it from importing the same excel file more than once. Each import file should be advanced by the next day
of the last record of the table.
Hi Have this function in access that allows me to propmt for a file name to be imported.
On Error GoTo Err_Import_File_Click
Dim strFile_Path As String
Dim strTable As String
'Prompt user for file path
strFile_Path = InputBox("Please enter file path V")
'Prompt user for name of table to create for imported data
strTable = InputBox("Please enter name of new table")
'Import file, using inputted file path and table name
DoCmd.TransferText acImportDelim, , strTable, strFile_Path
If Err.Number = 3011 Then
MsgBox strFile_Path & " is not a valid path, please try again", vbExclamation, "Invalid File Path"
Can I adapt it to do 3 extra things.
1. Can I add an Import Specification. Am having problems if the first line of what I am importing is numeric, the text values
on the lines that follow error.
2. Can I add in a bit where the filename is file location is filled out. i.e. c:/temp/ then all I have to put is the filename
3. I am only importing to one table. Can the table field already be filled out. i.e. It imports everything to table
I am importing a large number of files and need to keep track of the files that I imported from a the folder in a table so I
will know not to import it again. An option that is not available to me is to import the file from a folder and move or
rename (other process need the file so I cant move or rename the files). Bellow is what I am using to import the files. In
the if statement I'd like to add something that will update my list of files table with the fl.
Dim fs 'file system
Dim fl ' current import file
Dim fls ' import files
Dim fldr ' import folder
Set fs = CreateObject("Scripting.FileSystemObject")
Set fldr = fs.getfolder("Parentsubfolderfolder")
Set fls = fldr.files
or Each fl In fls
If Right(fl.Name, 4) = ".csv" Then
'DoCmd.TransferText acImportDelim, "Import Specification", "Master Table", "Parentsubfolderfolder" + fl.Name, No
I've been through Ken's excellent list of Excel import VBA.
However, I'm not very good with VBA, and could use some help. I have a number of poorly formatted Excel files, with different
layouts, etc. etc. etc., and I have everything I need to import it into Access and clean it up, but the one piece of the
puzzle I'm missing is this: Ken's scripts allow importing into multiple tables where each table has the name of the origin
worksheet, but does not give the ability to bring that worksheet name into the actual table.
What I need to be able to do is import every Excel sheet (with each sheet having anywhere from 1 to 10 columns), but rather
than go into separate tables, I need everything to go into one table where, in addition to the data from the spreadsheet,
each imported record also has a field showing the file name and the worksheet name.
I'm another bloke on a steep learning curve to understand access as a system. I have this as my current problem:
A client is emailed monthly .csv files which contain sales data for each salesman in the company. I want to be able to
automate an import this information to a database for later manipulation. The problem is that the information must go across
many tables, and that many of the rows/columns in the .csv file are not relevant at all. I don't fully understand the concept
of ranges, can I create and import them through vba? even if I can, many columns have to be ignored
I have implemented a file browser in a form, which passes a file name to a macro procedure, now how can I use vba to browse
the file, and choose which columns from which rows to import? I gather I can use an ADO connection to create an append query
but I have no idea how.
I've searched for what seems like a long time but only came up with an older .csv thread which (can't post link sorry) is for
only one table and not for 2007 (different?)
I have attached the screen shot of the error.Please help.
Getting these errors when importing CSV file to access.doc
(349.0 KB, 7 views)
Reply With Quote
01-07-2013, 11:39 AM
Windows XP Access 2010 32bit
Join Date May 2011
Location The Great Land
What field is the primary key? Why would the import records not have a primary key value?
Would have to examine your database and the import file. If you want to provide for analysis, follow instructions at bottom
of my post.
I have a macro in which I'm using TransferText action to import a tab delimited file. It works great, but I need the File
Name to point to a default UNC path that the user(s) set up and store in a table of default settings, i.e. tblDefault,
Any ideas how to get the file name to read the tblDefault.NetworkPath
As always, this bulletin board is a wealth of information and a real life saver!!!!!
I'm tying to get just the file name that get's selected in a dialogue box. I can get the path and the path and the file name
but not just the file name.
Here what I have so far:
'Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)
'Allow user to make multiple selections in dialog box.
.AllowMultiSelect = False
'Set the title of the dialog box.
.Title = "Please select a scan model that this can be modeled after."
.Filters.Add "All Files", "*.*"
If .Show = True Then
oldname = fDialog.SelectedItems.item(1)
NewName = "Llfsp2IRISEUCRTM2007RTMSorageFolderScanMode ls"
retval = 0
Set objFSO = CreateObject("Scripting.FileSystemObject")
retval = objFSO.CopyFile(oldname, NewName, True)
I have been using some code that allows a user to browse for a file in order to import it into Access. It works like a charm
as long as the data to be inported comes from another access file. The module that allows the user to select a file is simply
saving the path and filename selected as a variable. The import command for access looks like this below:
As you can see in order to import dbase you have to know both the path/file name combination AND the the file name on it's
own. I just don't know enough about coding in VBA yet to modify the code I'm using. I'm pasting that code in here. Any ideas
or thoughts on how to do this are appreciated.
' (Copy them to the (declarations) section of a module.)
Public Type OPENFILENAME
lStructSize As Long
hwndOwner As Long
hInstance As Long
lpstrFilter As String
lpstrCustomFilter As String
nMaxCustomFilter As Long
nFilterIndex As Long
lpstrFile As String
nMaxFile As Long
lpstrFileTitle As String
nMaxFileTitle As Long
lpstrInitialDir As String
lpstrTitle As String
flags As Long
nFileOffset As Integer
nFileExtension As Integer
lpstrDefExt As String
lCustData As Long
lpfnHook As Long
lpTemplateName As String
Public Const OFN_FILEMUSTEXIST = &H1000
Public Const OFN_HIDEREADONLY = &H4
Public Const OFN_PATHMUSTEXIST = &H800
Public Declare Function GetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (lpofn As OPENFILENAME) As Long
Public Function PromptFileName() As String
Dim filebox As OPENFILENAME ' open file dialog structure
Dim FName As String ' filename the user selected
Dim result As Long ' result of opening the dialog
' Configure how the dialog box will look
' Size of the structure.
.lStructSize = Len(filebox)
' Handle to window opening the dialog.
.hwndOwner = 0 'Me.Hwnd
' Handle to calling instance (not needed).
.hInstance = 0
' File filters to make available: Access Databases and All Files
.lpstrFilter = "Access Databases (*.mdb)" & vbNullChar & "*.mdb" & _
vbNullChar & "All Files (*.*)" & vbNullChar & "*.*" & _
vbNullChar & vbNullChar
'.lpstrCustomFilter is ignored -- unused string
.nMaxCustomFilter = 0
' Default filter is the first one (Text Files, in this case).
.nFilterIndex = 1
' No default filename. Also make room for received
' path and filename of the user's selection.
.lpstrFile = Space(256) & vbNullChar
.nMaxFile = Len(.lpstrFile)
' Make room for filename of the user's selection.
.lpstrFileTitle = Space(256) & vbNullChar
.nMaxFileTitle = Len(.lpstrFileTitle)
' Initial directory is C:.
.lpstrInitialDir = "C:" & vbNullChar
' Title of file dialog.
.lpstrTitle = "Select a File" & vbNullChar
' The path and file must exist; hide the read-only box.
.flags = OFN_PATHMUSTEXIST Or OFN_FILEMUSTEXIST Or OFN_HIDEREADONLY
' The rest of the options aren't needed.
.nFileOffset = 0
.nFileExtension = 0
'.lpstrDefExt is ignored -- unused string
.lCustData = 0
.lpfnHook = 0
'.lpTemplateName is ignored -- unused string
' Display the dialog box.
result = GetOpenFileName(filebox)
If result 0 Then
' Remove null space from the file name.
FName = Left(filebox.lpstrFile, InStr(filebox.lpstrFile, vbNullChar) - 1)
'Debug.Print "The selected file: "; fname
'return the string of the file name
PromptFileName = FName
Hi. I have a filename stored in a table. I was wondering if there was a way to look at this file name and store it in a
different table but as an OLE object. Apaprently there is a way to do it which follows the logic of reading the record, doing
something to the record (in this case making it an OLE object), and the storing the changed record.
I am importing an excel spreadsheet into an Access table using the following code:
Dim FiletoImport As String
Dim dbs As Database
Set dbs = CurrentDb
FiletoImport = "l:HR Current.xls"
DoCmd.TransferSpreadsheet transfertype:=acImport, tablename:="(SE)_tbl_HR_Data_Raw", Filename:=FiletoImport,
it works fine but I need error handler for when the file is not yet in the right location i.e. the person using the db has
not saved the file in the correct location but still tries to run the import. Furthermore I would like to change the file
name to not be proceeded by the letter "l" but by a wilde card character in order for the db to work on other people's pc who
might not have named their drive "l".
Hi - I would like to output a ".snp" file to another drive and since I will be send a new file each week, I would like to
have the file name change by date: e.g. "data_031105.snp" and then "data_031805.snp" the next week etc.
I have search the forums and cannot find any solutions; however, I apologize in advance if this question has been asked
before and I missed it. Thanks for your help and enjoy the weekend.
after a little bit of advise, searched google but cannot find the answer,
I have a macro that exports a query to excel is it possible to change the file name to a text box on a form the query is
also filtered off the text box
I have a function that I created with a lot of help. It creates an array from files in a specified directory, dumps these
filenames into an Access table, renames each file with the correct extension and then imports each file. After this I have
some cleanup queries.
The original goal of this process is to rename and import files on a weekly basis through a form. File date and import date
would be assessed at the time that the file is renamed and imported. This part works perfect.
Here is the problem, there are appx 250 files from the past year that I need imported, which is why I have the array. But I
need the filename attached to the group of records, and the date that it was imported into the PC. Can this be done? I
tried doing this one by one, but every time I mess up, I have to start from the beginning.
Here’s is the code for the array.
Dim SQL As String
Dim DirectoryFiles() ' Array that contains dir listing
ffile = DIR("C:WINPATHRECEIVE-BILLE3*.REC")
Do While ffile ""
If ffile "" Then
ReDim Preserve DirectoryFiles(count)
DirectoryFiles(count) = ffile
count = count + 1
ffile = DIR()
For Each itm In DirectoryFiles