General :: Embedded Excel Files
Feb 14, 2013
pretty Excel sheet full of conditional formatting that is used as a vehicle assignment board where I work. I know I can embed the file into an Access form using an unbound object, and therefore "integrate" it into Access, however this creates a copy of the file, and therefore when people change the sheet, the original Excel file is not updated. Is there any way to embed the excel file in such a way that the Excel document itself is also updated? Basically some sort of an active, two-way link rather than what amounts to just importing a copy of it? I need to be able to pull data from various cells in that Excel file so it needs to be kept up-to-date.
View Replies
ADVERTISEMENT
Sep 11, 2012
So I've just started with a company where my job is to manage a ton of data. I am new to Access (2010) and am completely impressed by all the awesome things it can do. However, I am running into some issues with knowing the best way to get my current data (which is all in Excel spreadsheets) into Access.
We are working with about 68 customers in 6 different focus areas (or areas of improvement). To make it easier for them, my company has not required them to enter all their data for each area into a centralized database. Instead, we are pulling reports (into Excel) from 3 different databases. The customers who are not currently submitting data to these databases are sending us Excel spreadsheets with their data (they send a seperate spreadsheet for each focus area). So you can see where my problem lies ... NOTHING is the in the same format. Even then the Excel spreadsheets look different from month to month (and customers submit a new, updated form every month with the current month and previous months) because the people managing the data before I came along changed the format a few different times and the customer has the ability to change them too.
Basically, I would really really really like to be able to just link the Excel files to my database, but I am struggling knowing how to do that with my data in so many different places and forms.
View 3 Replies
View Related
Feb 23, 2013
My computer crashed and I had to reinstall Access 2010 a new computer with Windows 8 and put my Access files on the computer from my backup USB drive. If I attempt to open the Access files from the USB drive, the computer has attached the Excel icon in front of the file name, even tho the type of files says ACCDB, and attempts to open with Excel. I get the following message: "'the file is not in a recognizable format".
The interesting thing is that if I open Access 2010 first and then 'find' the same file that I moved from the USB drive to my desktop, the file will open even tho it has the Excel icon.
How do I change my Access back to where they have the Access icon preceding the name, rather than the Excel icon?
View 1 Replies
View Related
May 3, 2013
What is the strategy for doing this rather than the code (if it's possible)? I can code a fair bit of VBA in excel but I'm not too sure about what I'm doing in Access.
View 1 Replies
View Related
Sep 29, 2005
I have an embedded excel workbook on an Access form (Access 2002)
I would like to change the zoom of the excel "view".
I've tried this:
' xlCharts is an embedded XL workbook
xlCharts.Object.Parent.ActiveWindow.Zoom = 100
I get an error that says:
"Unable to set the Zoom property of the Window class"
However, if I READ the zoom level, it works fine. The zoom property of the window class in excel is read/write, but this may not be the case for embedded OLE objects. Is there another way? Does anyone have any ideas?
View 1 Replies
View Related
Mar 9, 2014
I would like to embed a blank worksheet in access form. I want to do a macro to change the cells value when I click on a button, but I don't know how to do it?
View 3 Replies
View Related
Nov 21, 2012
Many years ago I created a database that has one embedded image for each project.
I am now redoing this db in 2010 format and I want to extract these images and save them in a folder so I can link them to the new db based on the project ID number
The table is [Project main], the unique project ID is [R&D ID#] and the image file is stored in [Item image] as an OLE object. i would like to save them to C:db images
View 1 Replies
View Related
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
Oct 13, 2005
Hello,
I have a form named Welcome and have stored an unbound list box named lstExport which I would like to use to view and open (using the double click event) excel files that users will be saving in a specific folder on our server.
Can anybody help me with the code?
The excel files are saved in the following path:
s:sharedfiles
Thank you.
View 9 Replies
View Related
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
Jun 22, 2007
I have 100 or so 2-sheet excel workbooks. I need to import them all into an access table for analysis. They are all exactly the same format/layout etc but obviously have different data in them (they are customer satisfaction surveys). I only want to export 1 out of the 2 sheets on each workbook (the other is a front end, the data sits behind in sheet 2).
So, at the moment I have to go to 'get external data' > 'import' > select excel and then double click each file individually and then go through the import wizard. Now, I can get them all into one table but it's clearly time consuming.
Is there anyway of doing a batch import of multiple excel files to cut out the manual work described above? Or can anyone suggest a lateral get around?
Any help much appreciated. I should say that I am running excel 2003 and access XP (2002)
Matt
View 5 Replies
View Related
Jul 17, 2014
I have been trying to write a macro that will do the following:
- Look to a specific folder in my home drive (nb this may change)
- select all of the excel files that are in that folder
- select various cells in each of those spreadsheets - each spreadsheet is formatted the same with the same structure. The cells are random, e.g. D6, I22, H4, K4, D17, so I cannot select a whole range
- copy these cells and paste them into one row of a database
View 3 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
Sep 5, 2012
I have linked to excel documents into access. They contain the same header rows but of course with different data.
Example: First name: Last name: Phone number:
How do i merge the information to a new table with the same header rows and the information from the two excel files.
Example new table with: First name: Last name: Phone number:
View 1 Replies
View Related
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 14 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 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
May 21, 2015
I have a basic access query (MACs Report Template) that I need to export into multiple excel files based on the 1st field (Plan ID).
Example:
PLAN ID
Number
Amount
Status
AM141
12345
100
Disconnected
AM141
54321
5000
Active
AM142
11122
2000
Disconnected
AM155
22334
500
Disconnected
I need this to create a spreadsheet for each unique PLAN ID. Ideally i want it to export the following:
MACs Report AM141 20150521.xls (both records above should be in this report)
MACs Report AM142 20150521.xls
MACs Report AM155 20150521.xls
View 3 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
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
Aug 7, 2014
Ive tried making command buttons for each of the three tables that when you click on it, it automatically updates the tables, but it doesnt seem to be updating them. Is this the right code for that to happen?
Private Sub Command0_Click()
Dim strFile As String
DoCmd.SetWarnings False
' Set file directory for files to be imported
strPath = "C:SharesPublicStaff Public FilesBrandon PenlandUS Food Product Prices Newest"
' Tell it to import all Excel files from the file directory
strFile = Dir(strPath & "*.xls*")
[code]....
View 1 Replies
View Related
Sep 4, 2007
Folks I need your help; just wanted to get your opinions here.
I work in a small engineering group and we have lots of reference documents in word and excel which we typically use for any projects. Currently, all these files are stored in Lotus Notes database. Unfortunately, they are pulling the plug on Notes license starting this fall. Therefore, I have been asked to see if there is a way we can store these files in Access as a repository and query the database whenever we need some information.
In order to avoid the database size getting too big, my thought was to store the .xls/.doc files as an “OLE object” data type, keep the files in local hard drive and create a link in a form to give the user to retrieve the information.
Do any of you have any suggestion on what is the best way to handle this?
Your input is highly appreciated.
Shan.
View 10 Replies
View Related
Oct 18, 2006
Is it possible to attach a WP, Excel, or Adobe file to a form, possibly by using a control key? How?
View 6 Replies
View Related
