Problem with unbound objectframe on a tabbed form

Question

Here is a weird one.  I have the following code in a Access 2013 DB

DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "4-SummaryFormLiscensesByDomainName_Crosstab", "x:\database\Office365Licensing\ExportFile\Summary.xls"

This works great, produces the file I am expecting.  I use some code to modify the excel sheet to add a row at the top and fill the cells with some formula.  I want totals to be there and some I perform some cosmetic formatting.  This works fine, it produces the exact spreadsheet I expect.

I need to display this spreadsheet.  I have created a unbound, Linked object frame on a tabbed form that is linked to the Excel file.   It worked fine for a long time.  However, It stopped working fully.  No errors are produced.

Below are the columns produced by my crosstab query, there are 10 total;

Below are the columns in my spreadsheet;

When I create the linked object frame, this is what I get;

It is missing 5 columns.  Also, the screen blinks at me, which is distracting.  Can anyone give me advice?

Answer 1

Hi,

How about rather than using an object frame, try linking to the Excel file as a table and then use a subform to display it?

Just a thought...

Answer 2

Hello tkosel,

I tried to link a xls file to an unbound object frame on a tabbed form and all the xls file's column could be loaded.

I did not have your data for exporting data to a xls file to reproduce your issue.

So, could you please share a simply database file so we could try to use it to  reproduce your issue?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 3

Terry,

Can I e-mail it somewhere?  How should I get it to you to look at.  I have the example ready.

Answer 4

Leo,

I thought about that, but since I never know how many columns there  will be and what their names will be don't see how that could work.  The only thing I know is the filename.  That is why I thought the linked unbound OLE object would be the best.

I am working on several solutions.  1 is to link to the excel file, display it as an icon, then have the user double chick on the icon to display it in Excel.  I don't exactly like this as it is slow and I don't want the user to be able to change things in excel. Plus, the spreadsheet opens in a window, not full screen.  The other is that I import the excel spreadsheet into a new table, allowing the import to assign field names.  I then wrote a query to Display * (All Fields) I created a form with a button to open each query in read only mode.  Not real pretty, but is seems to work OK.

Answer 5

Hi,

Although I can't try it right now, not knowing how many or the names of the columns in the Excel file should not matter when you create a linked table to it. For example, when you link an Access table, you can remove or add fields to the source table and when you open the linked table, you will see the changes. So, I am thinking a linked Excel file should function the same.

And as for displaying unknown columns in datasheet subform, you can simply create a query with the following SQL statement:

SELECT * FROM TableName

And then use the Query's name as the Source Object for the subform. It might look something like this: Query:QueryName

Just my 2 cents...

Answer 6

Hello tkosel,

You could share the file via One Drive and then put link here.

Share OneDrive files and folders

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 7

Terry,

I didn't want to sign up for a new account, and I didn't realize my microsoft account allows access to onedrive.  Thanks for the suggestions.

Here is my example

https://1drv.ms/u/s!AhkZS5ZtqlgLfWcC9u9BkDnI048

Answer 8

Hello tkosel,

Thanks for sharing the file and I have download it for testing.

I could see the existing unbound object frame and it do show only few columns.

However, the linked source doc is a file which does not exist in my pc, so I tried to create a new unbound object frame and linked to your share xls file for testing. It works to show all columns.

Have you tried to use an new unbound object frame for testing? Did you set the link source doc manually or code? If you used code, what's the code.

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 9

Terry,

I used a new unbound object frame on a new form in a new db.  I did the link source manually not with code.

If you used the source XLS file that I sent and were able to successfully link on your machine, wouldn't that indicate there is a problem with something on my machine?  I am using Access 2013, Office version 15.0.4823.1004

Answer 10

Hello tkosel,

Have you tried to update Access 2013 to latest version 15.0.4997.1000 or repair your office to test if it could work for you? Do you have any other pc with Access for testing?

Best Regards,

Terry

---

MSDN Community Support
Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact [e-mail].

Answer 11

Terry,

Thanks for continuing to follow this thread.  I have updated to the latest version.  I used both the quick repair and the online office repair.  Neither one fixed the issue.  I will use one of my other computer next week to see if the problem is there also, but only have the one machine with me this week.

Answer 12

I will add the comment that attempting to window 1 application (in this case Excel) inside of another application (in this case of course Access) - is problematic.  As you are finding out.  Perhaps a frame of some sort may work, but lots of people struggle with it.

Fundamentally a better approach, if at all possible, is just not to do it.  To instead rely on the operating system to provide windows to differing applications concurrently.  The role of Access then is to trigger that other window to open - but of course it is opening outside of the Access container.

I realize for a very high end windows application that my suggestion/philosophy is of no help.  But for the great many developers somewhat new to Access - we see them expecting that windowing excel, Word, Adobe inside of an Access object will be simple where actually it is quite involved and best avoided.