Connect to Excel from Access

[mjdemaris]

Is there any way to determine if an Excel file is being edited, and not just open?

I am creating connections from an Access database - for testing, possibly for importing data. I found that if the file is closed or open, I can still create a connection and read data, but if any cell in any sheet is being edited, then I cannot create that connection.

So, how would I determine if the file 1) is open, and 2) is actually being edited?

Then, I suppose I would need a timer to recheck that connection and import data when available. Is this possible as well?

 

[boerbende]

When opening a regular Excel file, I think you will receive a message saying it is opened already by somebody else (maybe VBA even blocks the opening),
But how to determine if somebody is changing it? Even with shared Excel files you don't know if a person on another computer is changing the file until he saves it.
I don't know your situation, but maybe you can consider to import the Excel file totally in a table and give the users access to this table with a frontend?

 

[sneuberg]

When opening a regular Excel file, I think you will receive a message saying it is opened already by somebody else (maybe VBA even blocks the opening),
But how to determine if somebody is changing it?

I noticed that if you use DoCmd.Transferspreadsheet to link to the Excel file it will link ok if the files is open, but if a cell is being edited you get this.

I got this message using with this code:

Sub TestLink()

DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "InputTableName", "C:\Users\sneuberg\Desktop\CheckBoxTest2.xlsx"

End Sub

The point being there's no error trapping in this code and so it appears this condition doesn't raise and error just this nasty message. I found that clicking Cancel on this hangs the Access application until the Excel file is closed. (See edit)

So I understand the need for a solution to this. I hope some forum member knows of one.

Edit: I tried this again and Access wasn't hung up. I just didn't wait long enough. You get the message three times and then the error 3051. I case your wondering DoCmd.SetWarnings False doesn't suppress the message.

 

[boerbende]

Workaround I can think of is to create a copy first of the excel with
FileSystemObject.CopyFile
open this copy
read the data
delete the copy after usage

Ben

 

[mjdemaris]

Good idea, Ben. Going to try that copying part. As for the front end, the users are used to using Excel for quite some time, and I just want to access some data.

Steve, thanks for the helpful troubleshooting tips.

 

[sneuberg]

Good idea, Ben. Going to try that copying part. As for the front end, the users are used to using Excel for quite some time, and

I didn't think that work around would work for you but for my own information I tried it with the following code.

Sub CopyFile()

Dim fso As FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
fso.CopyFile "C:\Users\sneuberg\Desktop\CheckBoxTest2.xlsx", "C:\Users\sneuberg\Desktop\Temp.xlsx"
DoCmd.TransferSpreadsheet acLink, acSpreadsheetTypeExcel12, "InputTableName", "C:\Users\sneuberg\Desktop\Temp.xlsx"
Set fso = Nothing

End Sub

and it works, i.e, the spreadsheet get copies in spite of the fact it is being edited. The copy has the values prior to the edit of course. This code requires the Microsoft Scripting Runtime reference or you could change it to late binding by changing

Dim fso As FileSystemObject

to

Dim fso As Object

 

[mjdemaris]

Steve, nice work. I am not familiar with late binding. Apparently this means I do not need the Scripting Reference, but how would this affect any other FSO properties/methods?

 

[sneuberg]

I suggest you doing some reading on late binding. So you can decide for yourself if you want to use it.

References:

http://sourcedaddy.com/ms-access/late-binding.html
http://word.mvps.org/faqs/interdev/earlyvslatebinding.htm

 

[Rx_]

Just a suggestion. Use Early Binding during development, then after the product is completed switch to late binding. This has saved me a lot of brain cells that can be wasted on television later.

My present contract involves a lot of SQL Server programming to provide recordsets for financial forecasting, actuals, projections. The user interface is MS Access with linked tables. The "automated reports" are Excel. The reports included all of the advanced custom formulas, crosstabs, custom sorting and grouping to provide a simulated Tree View and more. Bottom line, pages and pages of vba code in MSAccess to create the Excel. The verbose formulas allow executives to conduct complex "what if" planning each week over a standard flat report with fixed data and summaries.
The budget and forecasting tool manages several billions of dollars. The first quarter it was claimed the entire system increased forecasting efficiency by over 10%.

The Access report generator is designed to run on a desktop, or unattended via jobs on a server.
I truly am one of the last dinosaurs in the Access / Excel vba world. Please don't pet or feed the exhibit. LOL

 

[mjdemaris]

Rx:
It seems that late binding is needed if you don't know the exact object you will be referencing, or not knowing the objects' versions. According to MS, late binding is much slower.

Steve: thanks for the links! The MS page is the most descriptive MS article I have read so far - perhaps I have not been searching the proper MS sites! Usually I get small articles published by GitHub.

At this point, I know exactly which object I want to use - though the version may change over the years as MS rolls out new Office products.

Why do I need to access the data in Excel? Well, that is the current platform we are using for purchase requisitions, which also includes a history of what has been ordered over the years. Those particular historical spreadsheets are not normalized to fit into database tables (also complete with spelling errors and variations). As I am developing an Access based system, I am attempting to read the historical data from Excel, somehow manipulate it enough to drop into a couple of tables, from where users can search their history for reordering.

I am not sure that this is a vain attempt, because of the amount of spelling variations and trying to normalize Excel's data into about 3 tables all linked somehow.

(I feel like I am joining the dinosaurs!)

In the long run, I hope to move to SQL server, and either use Access as the front end or create something in C# - but I have just begun learning that language.

Rx: do you use class modules? I have been thinking about that lately in an attempt to clean up my code and make it more efficient.

 

[sneuberg]

According to MS, late binding is much slower.

I was concerned about this when I changed a lot of Word Automation code to late binding so I tested it as I want to know how much slower. It ran faster with late binding. So I suggest testing your code if speed is driving a decision.

Here's a hand function you can put at the top of a standard module to time code.

 Public Declare Function GetClock Lib "winmm.dll" Alias "timeGetTime" () As Long

To this you would do something like:

Dim StartTime as long
StartTime = GetClock

'Code being tested

Debug.Print GetClock - StartTime & " milliseconds"

 

[mjdemaris]

Thanks, Steve. I am not really bothered by the time it takes, not until I move beyond these two small applications. But, that was a fairly detailed explanation, and I realized that I already am using both types of binding - and now I know why Intellisense wasn't helping me out with some of the late bound object members!