Solved

I need assistance reading values in an embedded Excel File in Access

Posted on 2014-12-21
12
221 Views
Last Modified: 2015-02-09
Hi Experts,
I found the code below on line, which allows me to open an Excel file as an Embedded OLE Object in ACCESS.  
Now that I load the Excel file , I want to:
 
1. be able to manipulate the Excel file from within EXCEL
2. read/loop through the updated data in the Embedded object, and Save the data to a table in my ACCESS database I click on a button.

Private Sub cmdOLEAuto_Click()
   On Error GoTo Error_cmdOLEAuto_Click
   With Me![OLEExcelSheet]
      .Enabled = True
      .Locked = False
      ' Specify what kind of object can appear in the field.
      .OLETypeAllowed = acOLEEmbedded
      ' Class statement for Word document.
      .Class = "Excel.Sheet"
      ' Specify the file to be embedded.
      ' Type the correct path name.
      .SourceDoc = "c:\OLETEST.xls"
      ' Create the embedded object.
      .Action = acOLECreateEmbed
   End With
Exit_cmdOLEAuto_Click:
   Exit Sub
Error_cmdOLEAuto_Click:
   MsgBox CStr(Err) & " " & Err.Description
   Resume Exit_cmdOLEAuto_Click
End Sub

Open in new window


How can I do this?  Thanks in advance
mrotor
0
Comment
Question by:mainrotor
  • 3
  • 2
  • 2
  • +3
12 Comments
 
LVL 84
ID: 40512594
be able to manipulate the Excel file from within EXCEL
Do you mean you want to do that while someone has it open in Access? If so, then I don't believe you can do that.

Why not just link the Excel workbook to Access, and grab the data that way?
0
 
LVL 30

Expert Comment

by:hnasr
ID: 40512600
Will dealing with the file as a linked table be of any good?
This way you update in excel, and manage in access.
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40512748
Is there a good reason to embed the excel file?

Normally the manipulation part is standard, just double click on the OLE Object. You are however restricted by the control size which is a pain.
Or better, right click the OLE then WorksheetObject Open (not Edit!)


To read the data from the embedded excel object you need to open it:
in your button code something like this:
Edit: corrected typo in code
Private Sub Command2_Click()
    Me![OLEExcelSheet].Verb = acOLEVerbOpen
    Me![OLEExcelSheet].Action = acOLEActivate
    Set Xl = GetObject(, "Excel.Application")
    Debug.Print Xl.Cells(1, 1).Value
End Sub

Open in new window

0
 

Author Comment

by:mainrotor
ID: 40512969
Scott and hnasr,
Can you please provide a sample of how I would do what I need to do, using a linked Excel workbook.

Thank you.
mrotor
0
 
LVL 7

Expert Comment

by:Gauthier
ID: 40513240
You mean there is no need to embed:
- You are dealing with a single excel file
- When moving around the access file in the file system, having to redefine the link is not a problem
Then in the Access DB, go to the ribbon tab External Data, Click Excel, and follow the wizard...
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 30

Expert Comment

by:hnasr
ID: 40513329
Here is a sample.
Edit in excel, and mange in access. You may query, create form, report, and create new tables.
The example was created in D:\linked
0
 
LVL 34

Expert Comment

by:PatHartman
ID: 40513370
I am confused by your process.  Why are you storing the Excel object in the Access database? Or, why are you manipulating a stored object?  Access isn't a particularly good file manager.  I would use Access to store templates that I use to create workbooks and then populate but once the workbook is populated, I would put it into a directory somewhere for the user to control.  If Access manages the data, you are essentially storing it twice.  Once in a table and again in the spreadsheet.
0
 

Author Comment

by:mainrotor
ID: 40520172
Hi Pat and other Experts,
I am not storing the Excel object in my Access database.

Here is step-by-step that will explain why I need the Linked or Embedded Excel in my Access 2013 application.
My application will be used to schedule employees for a given job.  The application will use the following two tables:  tblEmployeeAvailability, and tblScheduledEmployees.
Step 1: Select a job,  to schedule, from the Job combo box  (this will display the job's start and end dates)
Step 2: When the Job combo box's On Change event executes:
•      The employees available to work the job will be selected from table tblEmployeeAvailability
•      The available employees will be loaded into an embedded or linked Excel spreadsheet (see image below)
Initial Excel workbook
Step 3:  I want to then, manipulate the Excel workbook to mark the dates I want to schedule the employees to work  (see image below)
Marked Excel workbook
Step 4: Click the Schedule Selected Employees button.  This will read the manipulated Excel workbook and save the data into the tblScheduledEmployees table.
NOTE: the Excel workbook will not be saved in the database.

If there is a better, much easier way of doing this, please let me know.  
I don't really want to do it this way, but i don't know how to accomplish what i need to do by just using Access controls.

Is what I am trying to do possible?  If so, how?

Thanks in advance,
mrotor
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40520322
IMO it would be far simpler to use temporary tables to handle this.

In Step 2, after determining which employees are available for work, you would insert them into a temporary table. For example:

With CurrentProject.Connection
  .Execute "DELETE * FROM TempTable"
  .Execute "INSERT INTO TempTable(Employee, Col1, Col2, Col3) SELECT Employee, '', '', '' FROM EmployeeAvailibilty"
End With

Now create a Subform that is based on TempTable, and show that. Allow the user to enter an "x" into the rows where you want the Employee to be scheduled. You might consider using a Continuous form, since you could then use the Header of that form to include Labels to show the names of those columns.

When finished, use the data in TempTable to write back to your tblScheduledEmployees table.
0
 

Author Comment

by:mainrotor
ID: 40547556
Scott,
I think you are correct.  I will probably use temporary tables for this.  Will post my results later.

mrotor
0
 
LVL 45

Expert Comment

by:Martin Liss
ID: 40597933
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now