Modules & VBA :: Import More Than 300 TXT Files And Add Filename Into Empty Field

Aug 14, 2015

I need to import 300+ individual .txt files into a MS Access DB table (named "tbl_input"). Each of the the .txt files have a naming convention similar to this:

Pink_Floyd_YYYYMMDD.txt

Because this initial import is so large and I would like to avoid doing it manually, I need to be able to:

import each of these files into a single table
delimit the text in each based on the "|" symbol
add the filename into an empty field

View Replies


ADVERTISEMENT

Modules & VBA :: Import Csv Files And Rename Them After Import Into (imported) And (failed)

Jul 17, 2015

I have the following piece of code for importing the .csv files from a selected folder and then renaming them into 'imported & filename' if succesful and 'failed & filename' if import failes for whatever reason (bad formating, etc.).

The problem is that the first time it encounters a 'bad' file after another, instead of going again to the Error handler, the command

Code:
DoCmd.TransferText acImportDelim, , "Activitate", filename, True

gives me the default MS Access error, namely 2391. I would like it to go the the Error_handler again ang follow the course of actions and rename the files into 'failed' and 'imported'.

Here's the code:

Sub ImportActivitate()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String, sFullName As String, sFilename As String
DoCmd.SetWarnings False

[code]....

Also, how can I make the routine stop cycling through the files after I renamed them all?

View 3 Replies View Related

General :: CSV Files - Importing Filename That Changes

Jul 2, 2015

I need to import a number of .csv files every day and overwrite the old tables with the data from the new .csv files. However, each day, the filenames will change.

For example,

Day 1: Import fileA 1.csv, fileB 1.csv, fileC 1.csv
Day 2: Import fileA 2.csv, fileB 2.csv, fileC 2.csv
Day 3: Import fileA 5.csv, fileB 5.csv, fileB 6.csv

The files will be named fileA [#].csv, fileB [#].csv, fileC [#].csv, and so on, with a different number each day (with no pattern to the numbers).

Is there a way to write an import specification or VBA script that will import these? Perhaps something that allows a regular expression in the file path so I can import "C:fileA*", "C:fileB*", etc.?

View 5 Replies View Related

Modules & VBA :: Import Files Listed In A Table

Jul 20, 2015

I have a folder that gets updated with a few new xml files every day. I need to import the data in these files into a table. The names of the files to import is in a field (FName) in a table (tblFiles).

The code that loads up the files is this:

Application.ImportXML DataSource:="C:ImportXML FILES" & FileName & ".xml", _
ImportOptions:=acAppendData

Up until now I specify the name of the file in an inputbox, so I have to enter a bunch of names to get it done.

I am looking for a way to get the filename from the table and then load up the file automatically.

View 1 Replies View Related

Modules & VBA :: Import Data From Multiple Excel Files

Apr 24, 2014

I have a requirement to create a piece of vba that will open all xlsx files in a folder one at a time then import the data in a range (sheet1!A1:G14) into a table named Weekly Input.

View 4 Replies View Related

Modules & VBA :: How To Import Data From Text Files Into Access

Nov 6, 2014

I have a text file that details every single incident that happened in the system. This means that it is extremely messy and non-comprehensible to a normal person due to the use of codewords and all.

Is it possible to scan through the document and insert the data into access, such that the different keywords are put as seperate events?

Ps, the number of characters between each event and data may not always be the same, so seperating them by that is not the way to do it. However, when I open the file in notepad++, I can confirm that each event is 6 lines.

E.g. Line 1 = Event timestamp, Line 2 = Event Name, Line 3 = Acknowledged or not, Line 4 = Acknowledged by who, Line 5 = Event Details, Line 6 = Application Owner.

Basically, the 6 lines are the 6 columns that I would need in my table. So, is it possible to separate out and read every 6 lines of data in the text file?

View 14 Replies View Related

Modules & VBA :: Looping Through Files - Import Specified Cells Into A Temp Table

Jul 22, 2015

I am trying to write a VBA Module that will look in a directory for Excel files with a specific file prefix, and then import specified cells into a temp table. Once it processes the first file, it copies the file to another folder, and then kills the file. However, when I run my current code, it goes through the loop the first time, but fails on the second attempt, because it is still looking for the first file it came across even though it has been moved. The code is pasted below:

Sub Count()

Dim xlwrksht As Excel.Worksheet
Dim xlWrkBk As Excel.Workbook
Dim nIndex As Integer
Dim strMvPath As String
Dim mvPath As String
Dim strFile As String

[Code] ....

View 6 Replies View Related

Modules & VBA :: Convert To Excel Files And Import Into Access Database

Aug 7, 2015

I have files that have extension of TSV which are text files but viewable in exel. I figured out a way for the user to click on a button in Access which does the following

1. Run Macro in Excel: The macro prompts the user to select the TSV file. After selection, macro opens the employee.tsv file in the excel (with excel being invisible) and saves it as employee.xls

Code:
Sub SaveTSVtoXLS()
Dim myPath As String
Dim myString As Variant
Application.DisplayAlerts = False
With Application.FileDialog(msoFileDialogOpen)

[Code] .....

2. Imports the Excel file (employee.xls) into two tables: tblEmployee and tblDepartment using the following codes.

Code:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryDepartment", selectFile, True - 1, "A1:C2"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml, "qryEmployee", selectFile, True - 1, "A1:AE2"

Everything is working flawless except that the user has to select the file three times:

1 time for the tsv
2 times for the xls file

Is there a way that the user can select the file only once (tsv file) or at least only twice one of the tsv file and the other for the xls file?

View 8 Replies View Related

Modules & VBA :: Import Multiple Excel Files With Different Number Of Sheets Into Table

Jul 12, 2015

some code I've come up to in order to import multiple excel files each of them with a different number of worksheets into an access table. The procedure is called from an Access database. The problem I have with the code is that when it encounters a workbook with only one worksheets (e.g. Sheet 1) it gives the error that "Sheet 2$" is not a valid name. When geting to a workbook with 2 sheets it says that "Sheet 3$" is not a valid name and so on and so forth. Is there a way to "check" the number of sheets in the workbooks and when it has only one sheet to transfer it and go to the next file?

Below is the code:

Code:
Sub ImportExcelFiles()
Dim strFile As String 'Filename's
Dim strFileList() As String ' File Array
Dim intFile As Integer 'Number of files
Dim filename As String
Dim path As String
DoCmd.SetWarnings False
path = "D:Tranzactii"

[Code]...

View 3 Replies View Related

Modules & VBA :: Import Multiple Files To Multiple Tables On Button Click

Sep 20, 2014

I made a database that in one of the forms, I like by clicking on a button the user be able to select 5 excel files with different file names (in the same directory) and then based on the imported file's names, it be stored in 5 different tables.

At the moment by using the bellow code, I can import multiple files (with the same formats) only into one table . My vba code comes as follow:

Function GetAllFiles()
Dim fd As Object
Dim strFilter As String
Dim lngItems As Long

Const msoFileDialogOpen As Long = 3
Const msoFileDialogViewDetails As Long = 2

[Code] ....

View 4 Replies View Related

Modules & VBA :: Import Multiple Fix Width Text Files - Eliminate Multiple Headers And Footers

Aug 1, 2013

I am trying to build a newer database 2010, based on an older one,2000, that has been locked tight and I cannot see the modules to kinda get a reference of where to start. I am trying to find a VBA code that will allow me to import a several text files to one table. The text files are all in the same format but I cannot remove the page headers and footers to get the table to look right. I have attached an example of the text file i am trying to import but it is a stripped down version for information protection.

Also, it appears in the old Database Table once imported as:

J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Smith 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date
J.Adam 1234 01 ABCD ABCD HGJV 2345 ABCDE ABC6 Qual Date Date

If I could import the text files and end up with a table like this, it would be all i need as i could run all the queries i need from this.

View 3 Replies View Related

Modules & VBA :: How To Recognize Empty Field

Sep 24, 2013

Why can't I get this simple piece of code to work?

Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = Null Then txtLoaned = 1
End Sub

Code:
Private Sub txtDateBack_AfterUpdate()
' updates loan status
txtLoaned = 0
If txtDateBack = "" Then txtLoaned = 1
End Sub

View 4 Replies View Related

Modules & VBA :: Check Whether First Line Of A Memo Field Is Empty

Feb 10, 2015

Our quotation tool, which is built in access 2003, has a memo field, which is extracted in a .txt file, to be uploaded by another system.

When the user starts the memo field with a blank line, by hitting the enter key, the upload file writes the memo field as a new line, separating it from the line indicator and thus giving errors.

My question would be : How would I determine in VBA, whether the first line of the memo field is blank.

View 4 Replies View Related

Modules & VBA :: GET Filename Directly From Path

Sep 18, 2014

For instance, I got some dynamic length path with filename as follows:

F:OUTDOOR9-8W1250832 ABCworking 1.3-0.6 BOARD 720.tif

Is there any easy way to get only the "working 1.3-0.6 BOARD 720.tif", which is the desired filename.

View 2 Replies View Related

Modules & VBA :: Set Filename / Location In Save As Dialog

May 7, 2015

The code below sets my printer to "Cute PDF writer" and then opens a report in print view, which creates a PDF version of my report. So far so good. But just before the last step, it brings up the Save As dialog box, asking for a filename and location.

Three points are important:I always want to call the PDF document "Specialty Report".I always want to save it to C:Documents and Settings80435 Desktop. I always want to overwrite the previous version.

Is there some code I can insert that would achieve this automatically with no further user interaction i.e. user clicks a button and the PDF file is created/overwritten?

Code:

'Produce the report
Dim defPrinter As String, NewPrinter As Printer
defPrinter = Application.Printer.DeviceName 'Get the default printer name
Set NewPrinter = Application.Printers("CutePDF Writer") 'Create a new printer object
Set Application.Printer = NewPrinter 'Set the default printer to the new printer
DoCmd.OpenReport "Specialty Report", acViewPrint 'Open the report in print view (which will PDF it)
Set NewPrinter = Application.Printers(defPrinter) 'Reset the printer back to the default printer

View 3 Replies View Related

Modules & VBA :: Inserting Filename And Path Into Table

Jan 8, 2014

I have a table named tbl_imagepaths with fields:

ImageID
File
Folder

The command button has the code below, it works great and pops up with a window with the file name and path. I now just need the code to insert the file name and path into the table....

Code:
Private Sub Toggle7_Click()
Dim f As Object
Dim strFile As String
Dim strFolder As String
Dim varItem As Variant
Set f = Application.FileDialog(3)

[Code] .....

View 7 Replies View Related

Modules & VBA :: Button That Automatically Save A Report As PDF And Generate Filename

Oct 8, 2013

Is it possible to create a button that automatically saves a report as .pdf? And automatically generates the filename.

Like 0810-544 (0810 is the date and 544 is the ID from the form.)

View 14 Replies View Related

Can't Import Dbf Files

Sep 18, 2007

I get this message when I import dbf files.

"The Microsoft Jet database engine could not find the object suitability.dbf. Make sure the object exists and that you spell its name and the path name correctly."

Can someone help? Thanks.

View 2 Replies View Related

Modules & VBA :: Export To Excel With Variable Filename Based On Query Result

Jan 30, 2015

My access is 2013.

Suppose there's a simple query which has two fields, "year" and "graduates", where "year" can be grouped by "2012", "2013", etc and "graduates" are individual names.

How can I export the list of graduates to multiple excel files, with the filename based on "year"?

I have tried to set the output file in macro as "c:desktop" & query.year & ".xls"

But it's not working and the output filename is exactly "&query.year&.xls "and the file contains all year and all names.

View 14 Replies View Related

Modules & VBA :: Loop Through Files And Then Compare With Files In Database Table

Nov 11, 2013

I have to write a code for my database,i have folder with files "pending Review" and a table with column "tblExcelLocation". when i run my database all the files from pending review folder goes to "tblExcelLocation" on a click of button.But,if the files already exists it should not insert those files and insert the rest.For this i tried to write a code but i think i m unable to do that .

Code:
Loop through files in folder
folderspec = "O:QA FilesQC ReportingPending Review"
Set fs = CreateObject("Scripting.FileSystemObject")
Set f = fs.GetFolder(folderspec)
Set fc = f.files

[code]...

View 7 Replies View Related

How To Import Dbf Files Automaticly?

Apr 9, 2008

Hello all
this is my first post. I've tried to get my info without any luck so i ask my question here.... hope it's ok.

I want to import some dbase files that comes from an accounting software and ask Access some queries. The queries part is perfect. My only problem is how can i ask (in a general menu of my Access application) access to go and get those dBase files?
I want the user to be able to have a button asking him : " Do you want to import new data?. And then it will update Access. I don't need to append the data just overwrite the old data. I'm not doing any manipulation of data on the files, just asking questions. (Hope i make sense...)

They are always on the same directory on the server.

Thank you for the help!

View 14 Replies View Related

How To Import Csv Files With Newlines

Feb 16, 2006

Dear experts,

I need to import a csv file into a Access table. Some of the fields contain newlines, these field should go into a memo field (they're > 256 chars) which are to be displayed in a report later.

Unfortunately, Access (and also Excel) treat newline chars (vbCrLf) as field separators, resulting in a lot of garbage... I'm interested in one of three tricks:
1) find a way to import the csv file with newlines
- or -
2) replace the newlines by unused chars before import, e.g. by '#!' and replace them after the import in the table
- or -
3) replace the newlines, and re-insert them back in by an expression in a query or a report

I have succeeded to import the file with the newlines replaced. I did not succeed to re-insert newlines (at least not with "Replace"), and I couldn't find a way reconstruct the newlines with an expression or in a report.

Has anyone an idea or better trick to do the job ?

Thankss....

View 1 Replies View Related

Cannot 'see' Excel Files For Import

Nov 22, 2004

Hello, i have installed Access 97 from an Office 97 CD on a new laptop and when trying to import a table and browse to my file location i cannot 'see' any excel files to import, it only allows me to see Acess files or ODBC sources. it worked fine on my previous pc using the same install method. any ideas? -thanks Scott

View 4 Replies View Related

Access Crashing Upon Import Of CSV Files

Aug 23, 2005

Every time I try to import a CSV file into my Access database it crashes. I've tried repairing it but the repaired database crashes as well. Any ideas on what could cause this?

Thanks.

View 8 Replies View Related

Import Or Link *.iif Files From Quicken?

Dec 29, 2004

Does anyone know how to import or link files from the Quickbooks Timer program? I've tried changing it to a *.xls file extension and that didn't work, tried linking vs. importing, etc.

Any suggestions?

View 2 Replies View Related

Making A Button To Import .csv Files

Feb 8, 2006

Hello, i want to make a button on my form to input .csv files. What is the best way to do this? I simply want to click the button and be asked for the csv location, i want the csv to be imported and be formatted. Whats the best way to do this?

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved