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 Replies
ADVERTISEMENT
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
Sep 22, 2015
I am attempting to automate the import of a .csv file from a given directory and copy and append it to another table in a different layout. Below is my code. The error that i'm getting is it can't fine the copyobject in the database...
Option Compare Database
Option Explicit
Function DoImportandAppend()
Dim strPathFile As String
Dim strFile As String
Dim strPath As String
[Code] ....
View 14 Replies
View Related
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
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
Jul 20, 2015
I have a few problems and I want them to be able to be done from switchboard:
1. Is there a way for me to export a particular report (after selecting it) to a closed excel template, that is formatted? It would open the excel template (that has a logo and column headings), export data to below the column headings, then save the file with a unique name?
2. Also, a way to import data from an excel file, after allowing the user to select file? Only data below the column headings mentioned above. Same data will be appended to existing table.
View 1 Replies
View Related
Jun 12, 2014
I receive a bunch of excel files with items to be added to a database. some of the items are to be added as new, some to be deleted and others to be modified.
I identify the action (add/delete/modify) by looking at a column in the excel file e.g. column A.
At this point I am fine with the add/delete because I can filter the data or bring all the files to a temporary table in an Access database via vba script and then running queries to do the rest.
My issue is that the spreadsheets have a lot of columns and for the modifications what they do is use the same file for instance, make changes on the cells (any cell) that need modification and then highlight, bold or underline only the items that need modification and ignore the rest of the file (a tedious process since I have to then open each file and manually modify the database).
View 1 Replies
View Related
Apr 20, 2015
I'm trying to copy the structure of a table to make a temp table. I'm using CopyObject (which also copies the data). So when I delete the data from the temp table, it also deletes data from the source table. Is the data linked? It should just be deleted from the temp table. Below is the beginning of the code. I've stepped through, and at the last step shown, the data in the source table deletes.
Code:
Dim strFile As String
Dim temp As String
Dim tbl As String
Dim db As DAO.Database
' error handle
On Error GoTo F_Error
[Code] .....
View 3 Replies
View Related
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
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 1 Replies
View Related
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
Jun 5, 2013
I have two tables that are formatted identically....
Table1 = MasterTable
Table2 = TempTable
I am taking a copy of one of the entry from the master table and Copying it to the temp table.I then open a form on the TempTable that enables the user to modify the content without affecting the information in the MasterTable.On Completion and Save I wish to take this modified content and update the MasterTable Using the Field "ID"
the tables for example look like this
ID NAME AGE DATE1 DATE2
I am guessing I need to use a
Code:
DoCmd.RunSQL UPDATE "MasterTable" WHERE "TempTable"
Type of command but not to sure on the correct syntax and as everything is the same the use of wildcards for all fields
View 10 Replies
View Related
Nov 19, 2013
I have some code that creates a table based off another table. This code works perfect but Im trying to alter my ID field and change it to an auto increment. How can I fix my code so that it alters my ID field?
Code:
Private Sub Command0_Click()
'OBJECTIVE: to build a table by extracting some fields from a main database
Dim rst As Recordset
Dim strSQL As String
Dim strSQL2 As String
Dim intCount As Integer
[Code] ....
View 5 Replies
View Related
Jul 2, 2013
I have a MainTable with Subform. The Subform is based on a Query. I want to save the data/Fields from subform to my TempTable..
MainTable Fields:
EmployeeNo
Name
Position
TempTable Fields:
ID*
Salary
Allowance
Subform Fields:
EmployeeNO (not Shown on Query results)
Salary
Allowance
I want the fields in my subform to be saved in TempTable..
View 4 Replies
View Related
Feb 26, 2015
I've been creating an automated way for users to import an existing DBF III file into an Access Table and then I'll be doing some other things after it's in. The user needs to be able to click a command button to open up a file picker, select the desired DBF and then click import and have it be imported into a new table.
Originally I had this all working because there was only one possible file name for the DBF file in each folder so instead of using a file picker I used a folder picker and supplied the file name in the VBA code as it was static.
However, I now need the user to be able to select a specific file and the name could be anything.
I've successfully edited the file picker to allow them to select a file however the importing is an issue. In order to do a TransferDatabase command I need the file path alone for DatabaseName and the file name alone for Source.
What would be the best way to split those two pieces of data up once the user has selected the file they need?
View 1 Replies
View Related
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
Aug 1, 2014
I have a looping error on this code. It all works fine until the very last row, on the last row it brings over the Claim_Number and the FacID, but leaves the Paid_Amount blank.
If I have 10 or 10,000 rows, its always leaves the Paid_Amount on the last row blank.
Code:
Function RunLWSPA()
Dim cnn As ADODB.Connection
Dim rstUplo As New ADODB.Recordset
Dim rstDiag As New ADODB.Recordset
Dim rstTemp As New ADODB.Recordset
[Code] ....
View 6 Replies
View Related
Jun 29, 2013
I am attempting to insert a record with selected data into a temp table and I am getting "Run-time error '3075': Syntax error in (comma)...". Here is the code:
Code:
Private Sub XferDataToTempTable()
Dim db As Database
Dim strSQL As String
Set db = CurrentDb
[code]...
View 7 Replies
View Related
Jun 27, 2012
Im familiar with Ms Access, but have never used VBA or Scripts. I have 37 Excel files with the same data and would like to import into one file. Data will be received on a monthly basis into the same directory and I would like to automatically upload the data into the same file in Access.
View 1 Replies
View Related
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
May 29, 2014
I need to extract a specific number of records into a table using a MakeTable or Append command using a temp variable, e.g. TempK&SA. Previously on the forum I was shown how code could be added to the OnOpen function to use a temp variable to select a specific number of records to report. ACCESS does not have the OnOpen function in the design view of a query like in the report. It does allow a SELECT TOP but only with fixed variables or percents (e.g. 25 in the code below).
The beginning code for the make table query (where 25 is the number of records added) is:
INSERT INTO [Output] ( RndNo, PointBiserial, BloomsTax, DateRevised, Exam1, Status, Exam2, Exam3, Exam4, [NCCPAKnowledge&Skills] )
SELECT TOP 25 TestBank.RndNo, TestBank.PointBiserial, TestBank.BloomsTax, TestBank.DateRevised, TestBank.Exam1, TestBank.Status, TestBank.Exam2, TestBank.Exam3, TestBank.Exam4, TestBank.[NCCPAKnowledge&Skills], *
FROM TestBank
WHERE (((TestBank.PointBiserial) Is Null Or (TestBank.PointBiserial) Between [TempVars]![TempPointBiserialLow] And .....
how to modify the code to allow a temp variable to determine the number of records to append to another table would be gratefully received. (This process then is repeated for a total of 7 append tables with different temp variables.)
View 7 Replies
View Related
Dec 18, 2004
Hello everyone!
Here is my problem:
I have an Access database that contains a table with three columns (A & B & C). Field A is for code values and field B is for the value for that particular code. Field C holds the case number. I have a folder that contains the information for over 5000 case numbers. Each case number needs to be placed inside of the table. Considering there are 5000 case numbers, I dont want to use the import wizard 5000 times.
Furthermore, the text file only has two columns (for A and B).
I am trying to create a VBA that will append rows to the table by going into each file and placing the information into the table. The first row for each file contains the case number. So when the VBA runs, it should open the text file get the case number from the first row (field B on the text file) and place that in the case number field of the table for the entire file; and then repeat this action for all of the files in the entire folder.
Any help that can be provided will be greatly appreciated.
Thanks.
View 13 Replies
View Related
Mar 25, 2014
I have a lot of Excel files and each of them has 3 sheets that I would like to import in Access 2010. How can I import them without having to do one by one?
I always get error on
Code : Application.FileSearch
View 1 Replies
View Related
Jan 23, 2008
Hello,
I have some data in excel which I am importing it into MS Access 2002. Each excel file (one worksheet per file) is imported into separate tables.
I want to combine all my imported tables into one table. Is that possible, if yes then how and if no then what can be done to get single table after impoting data from various excel files.
Cheers,
Mandeep
View 14 Replies
View Related
Jun 27, 2013
I am the first to admit I struggle with looping through records.
I am trying to loop through a table and collect external income data and write it to a new table.
My issue may be the way the table is laid out. ???
The attached pictures show the tables. The tblExtInc is where the data are. I am trying to create a table that looks like tblExtIncYr that shows each year and separate entries by item.
How would I loop through tblExtInc and grab ex:$2000 every year for upto 100 years but only get the $1000 for year 6,7,8 only?
View 2 Replies
View Related
Jan 28, 2015
I am writing the following code that will first of all display column headers dynamically using "Headers" field data from Access table and then find out the sum(volume) using column header and first column values. The following code works fine to display headers dynamically in Excelsheet from Access table but doesn't display sum(volume) in all the corresponding cells. As I can't attach the Access table so I have stored data from Access table to sheet named "Access Data" as attached. The sheet2 named "Report" should populate total volume .
Code:
Public Function Inputdata()
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim r As Long
Dim i As Integer
[code]...
View 2 Replies
View Related