Modules & VBA :: Importing Excel Files - How To Add Additional Columns
Dec 31, 2013
I need to add two additional columns for each imported spreadsheet, which are not in the original files.
First column, named 'Customer Name', needs to retrieve information from cell A6 in Excel file, which is not part of range "name" that is imported to Access. If necessary, I could somehow include it in this range but then I still would have a problem of how to repeat a value of A6 in each row of table in Access.
Second column, named "File Name", I would like to include an Excel file name from which data was imported. Again, I would like to repeat value for each row in the table, i.e. if I have 6 rows with data from file X, I would like to repeat X.xlsx in each row in column "File Name".
Code:
Dim strFile As String
DoCmd.SetWarnings False
DoCmd.RunSQL "DELETE * FROM Data;"
' Set file directory for files to be imported
strPath = "C: est"
[Code] ....
View Replies
ADVERTISEMENT
Jul 4, 2015
Access Version 2010
Excel Version 2010
I have 200 excel files in folder C:UsersLburchDocumentsHistoric DataTedan Data in Excel Worksheets - CopyAAA - Copy.The files only have one worksheet and the column format are the same. Worsheet name is different for every file though.Headings in first row.write a macro to import into 1 Access table so I don't have to do it manually.
View 14 Replies
View Related
Dec 1, 2012
I have a data file I am importing into MS Access 2010. One of the fields is a large text field. When i import that field into Access the text is getting cut off. How do I get the full text field to import without cutting off?
View 6 Replies
View Related
Jun 29, 2015
I was using Excel.We do transportation, I have two columns Which means 2 Records One is from address a to address b.And another from address b to address a it's not always like that The return can be to anywhere I want to make one table called 'locations'.I can set the relationships to both fields using that table or so I think But what about the last five years worth of data I can't seem to split my data correctly.
View 5 Replies
View Related
Dec 17, 2013
Currently I have a code that imports a single text file into a table without problems.
I need to be able to selecte multiple text files and import them at once.
Here is my current working code.....
Code:
Private Sub Import_Click()
Dim strFilter As String
Dim strInputFileName As String
strFilter = ahtAddFilterItem(strFilter, "Text File (*.txt)", "*.txt")
strSaveFileName = ahtCommonFileOpenSave( _
OpenFile:=True, _
Filter:=strFilter, _
DialogTitle:="Please select new file...", _
Flags:=ahtOFN_OVERWRITEPROMPT Or ahtOFN_READONLY)
DoCmd.TransferText acImportDelim, "Import Specs", "Data Table", strSaveFileName, True, ""
View 14 Replies
View Related
Jul 25, 2013
I have a few hundred access databases all with the same tables and structure, and want to import all the data in a 'mother' database (which i created with the same tables and structure).
I am using a sub that feeds a string to another public sub as follows:
Code:
Private Sub cmdImport_Click()
Dim strFolder As String 'source folder path for import
Dim strFile As String 'individual file path
strFolder = GetFolder(Environ$("USERPROFILE") & "Documenti")
If Len(strFolder) <= 1 Then Exit Sub
[code]....
View 3 Replies
View Related
Mar 17, 2014
I want a string of code that allows me to import a series of delimited text files into access using VBA. I have the first bit of code down:
Code:
DoCmd.TransferText acImportDelim, deltxtimptbl, "Delivery(local)", "msfs3109data1shareeveryoneprorep ranhistDelivery" & currentdate & ".txt"
The text files hold data for a specific days work. Each day has its own text file. I would like to be able to import the text files within a specific date range, specified by the user. Below is the total code i have for my form button:
rivate Sub Command0_Click()
Dim startdate As String
Dim enddate As String
Dim currentdatex
Dim count As Integer
count = 0
[Code] .....
View 12 Replies
View Related
Sep 19, 2005
Can I import data into a table that already has data in it? I want to add this new data to the existing information.
View 2 Replies
View Related
Feb 3, 2015
I am using Access 2010 on a Windows 7-64bit machine. I am trying to begin an employee database using the faculty template. It would be exceptionally useful if the "Add from Outlook" command that is built into the "Faculty list" form would fill more than just the name, email address, and phone number fields. There are fields on the form for Department which match available info from the Outlook address book, but isn't imported.How can I tweak the command to fill the additional fields?
View 1 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
May 4, 2014
I am using Access to save an Excel file in a particular directory. The code was created about 10 years ago and hence saves the file with .xls extension and I would like to save with a .xlsm extension. If I just change the .xls to .xlsm, in the code below, the file is saved but the Compatibility Checker comes up (I would like to stop this) and when the file is reopened I get an error message stating there is a file extension problem and the file won't open.
Code:
strExt = ".xls"
'If .txtFriendlyName <> "" Then
' strDestFileName = .txtFriendlyName
'Else
' strDestFileName = Left(objFile.Name, Len(objFile.Name) - 4) & MakeFilePosfix(Now())
[Code] ....
View 7 Replies
View Related
Dec 28, 2014
Is there a way of importing MS Excel Spreadsheets but starting from row 8. I am now getting lots of spreadsheets but i only need data from row 8. I have to manually delete the rows and save the spreadsheet then import. Some days i can have more then 1.
View 2 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
Jun 26, 2015
I have a database which I've creating but now want to make others available to update - in the past this has not gone well! The database uses data from two excel sheets and the problem has been people do not always check if these have been updated today before running and therefore the database gets loaded with old data.I have found code that will check and display a message showing datelastmodified for a file but what i'm trying to find is code which will check both files' datelast modified, if they are modified today then run the update macro, if one or both are not modified today, then to display a message saying that the excel is not updated.
View 7 Replies
View Related
Mar 25, 2015
I have a VBA function to syncsuppliers as below
Function SyncSuppliers()
On Error GoTo errhandle
Filename = DLookup("SupplierPath", "Setup", "SetupActive = True")
If Filename = "" Then
Exit Function
End If
Set xlapp = CreateObject("Excel.Application")
[code]....
The 5th row is where the problem is abbot and co will import n stop missing out the brackets (I need all the data). same for the last row A-BELCO LTD will import (HADAR LIGHTING) does not.
View 2 Replies
View Related
Jul 23, 2014
I am running this code to import an Excel sheet into a table in my database. This works fine if I set [HasFieldNames] to false. When [HasFieldNames] is set to true, I get the correct field names in the table, but don't get any data from the sheet.
DoCmd.TransferSpreadsheet acImport, , "txlsRevenuePayback", _
"s:ProgramsReportingPayback Revenue Table - Master Copy.xlsx", True, "Actual Revenue By Province!A12000"
View 14 Replies
View Related
Jul 2, 2013
I have a code that allows the user to select the file that they want to import, however it automatically imports the first worksheet into a specific table. Is it possible to change the code to make it import the first worksheet (or with a specific name) into one table and another worksheet into another table?
Code:
Dim dlg As FileDialog
Set dlg = Application.FileDialog(msoFileDialogFilePicker)
With dlg
.Title = "Select the Excel file to import"
.AllowMultiSelect = False
.Filters.Clear
[Code] .....
View 1 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
Jun 11, 2013
I'm writing to an excel file from two separate Access tables, for which I'm using two separate DAO.Recordsets in VBA.The first table has 190 columns, the second table has more than 66 columns, so more than 256 columns will be written to the excel file.So I'm not expecting any error due to any maximum of 256 columns, but still I'm getting an error while trying to populate the 257th excel-column:
Run-time error '1004': Application-defined or object-defined error.(The same VBA works fine when I use another table with less columns.)Even more strange: on another computer I did not get the error and I was able to populate more than 256 excel-columns.
Code:
Option Compare Database
Option Explicit
Dim xlApp As Excel.Application
Dim xlWkb As Excel.Workbook
Dim xlWks As Excel.Worksheet
Dim FileNameOut As String
Dim RowNr As Long
Dim ColNr As Long
[code]....
View 5 Replies
View Related
Jul 18, 2013
Im trying to export a query to specific columns in excel and im using this code.
Code:
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
[Code].....
I tried it on other querys and it works but doesn't work on this specific one. I get an error on the "set rst = currentDb" line. And to my knowledge it doesn't work because I have a between two dates filter in it. So when I run it, I get an error saying I have too few parameters, expecting 2.
Also it seems to be exporting the lookup id's not the value when exporting(on the query that it did work on)
View 5 Replies
View Related
Apr 14, 2014
I am using MS Access and Excel 2007. I have a query that I need to export to a specific sheet in excel, and the vba is working great and doing what it should. Problem is, some of the data is being exported as numeric, not as text which is what I need. I have several tables related to one another and the fields that were created with the LookUp Wizard and the ones that are not exporting property. From what I have read online, the data that is being exported is from the bound columns, which are the primary key "ID" fields. I have read online that I need to JOIN tables so that Access knows how to identify the foreign keys, but no matter how I do that, it is still exporting the "ID "fields and not the description.
Here is the SQL that I am trying to export:
SELECT tblSampleNew.*, qryProjectSearch.Company, qryProjectSearch.ProjectName, qryProjectSearch.SampleDate, qryProjectSearch.SubmissionDate, qryProjectSearch.SamplePriority, qryProjectSearch.DueDate
FROM qryProjectSearch INNER JOIN tblSampleNew ON qryProjectSearch.IDNumber = tblSampleNew.IDNumber;
the qryProjectSearch.Company is one of the fields that is exporting incorrectly. I have tried to JOIN it to the original table by adding in this expression to the SQL above:
FROM qryProjectSearch INNER JOIN tblClients ON qryProjectSearch.Company = tblClients.ID
This gives a mismatch error, since one field is text type and the other is number, but even when I adjust that, it is still exporting as numbers and not text.
The annoying thing is that when I go to External Data --> Export to Excel Spreadsheet, every exports as text like I want!
View 6 Replies
View Related
May 25, 2005
Hi
I am having much trouble importing csv file into access 2003
My freewebs site have a registration form
The forms are then downloaded into my pc as csv files
According to freewebs, the csv file is access friendly and can be imported into access
so I tried for about a million time and still no luck
where did i go wrong?
this is what i did
help !
regards
frontpageplanners
View 14 Replies
View Related
Feb 24, 2008
Hello,
I have a excel file which is a combination of to files (a .csv and txt) which I need to manipulate using access to get certain output. I created 2 tables for the 2 files in Access and then combined the 2 files by joining them on some common attributes.
Sample records from the excel file:
There are two fields..samaccountname and groupmembership which I need to display in a single line..
Accountname GroupMembership
trs 92 CN=US-HTSU Disable Mass Storage
Devices-S-D,OU=Call Centers,OU=Desktop
Access Groups,OU=US HTSU,OU=US
Groups,OU=US,DC=HNAH,DC=ADROOT,DC=HSBC
Wherever there is group membership, I want
that comma separate and parsed for a user in one line
'CN=US-HTSU Disable Mass Storage Devices-S-D,OU=Call Centers,OU=Desktop
Access Groups,OU=US HTSU,OU=US Groups,OU=US,DC=HNAH,DC=ADROOT,DC=HSBC
'CN=HNAH Generic Accounts-S-G,OU=Department Groups,OU=US HTSU,OU=US
Groups,OU=US,DC=HNAH,DC=ADROOT,DC=HSBC
I just need the following:
Tsr012,"US-HTSU Disable Mass Storage Devices-S-D,HNAH Generic Accounts-S-G"
Is there a command or way to do this in Access or even SQL Server?
Any help would be much appreciated..
Thanks!
Gaurav
View 2 Replies
View Related
Mar 17, 2015
I'm pretty new to Access and am trying to create an Asset database for our IT department. I'm trying to import a csv file that has all of our Android tablet information in it. One of the fields on this csv is "Office location" to let us know what office its currently at.I have a table already created for all of our offices called Offices.
All I'm trying to do is import the Android Tablet csv to create a new table called Tablets and have the "Office location" field from the csv link to the Office tables list of offices. I can import the csv file fine and have it create a new table called Tablets. The "Office location" field is just set to short text and isn't actually linked with the list of offices from the Office table.I tried linking them by changing the Office location field to lookup and pointing it to the Office tables field that has all of our offices locations, but receive an error. I'm guessing this isn't how I'd get this to link to the list of offices. I'm trying to have the Office locations field link to the list of locations automatically so that I don't have to create a new field and then make it a lookup field and manually change it for every Tablet that I have in the Tablets table.
View 1 Replies
View Related
May 1, 2006
Hi!
I'm trying to import csv files into an Access 97 database, but the filename is different each time otherwise I would just use a commandbutton with the transfertext macro.
I've been searching for help to browse to a file and keep coming back to the same few pages but not being a programmer I can't figure out how to use the code.
Can anyone help me with simple instructions on how to do this?
Thanks.
Anna.:confused:
View 5 Replies
View Related
May 12, 2006
Hi, I’m trying to Import an external CSV file in to Access and then Update/Add the record into a table. I need to be able to do it using SQL and I’m not allowed to touch RecordSet!
Does anyone know How I could do this or where I would be able to find help on this.
Thanks
coops1204
View 1 Replies
View Related
