Import to Access from READ ONLY Excel

[Cameron.Turner]

To the hive mind....

I am importing a named range from hundreds of excel workbooks in specific file locations into an Access Database. The code below works fine except when someone in the business has the excel file open. The import function doesn't work because the file is READ ONLY. When the code is finished, all the excel workbooks that I couldn't import from are open. I tried some code to make it open all files as READ ONLY, but as there are hundreds, I ran out of memory and my computer cried.

Is there a way to ignore the Excel READ ONLY lock and import anyway? Any help you can give would be appreciated!

Function ImportExcelFiles()

DoCmd.SetWarnings False
DoCmd.RunSQL ("Delete * FROM Prod_Mod_Data")
DoCmd.SetWarnings True

Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean

blnHasFieldNames = True
strTable = "Prod_Mod_Data"
Set xlObj = CreateObject("Excel.Application")

strPath = "C:\Example\"
strFile = Dir(strPath & "*.xls")
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, , strTable, strPathFile, blnHasFieldNames, "Data_Extract"
strFile = Dir()
Loop

MsgBox "Files successfully Imported"

End Function

 

[DCrake]

Options:

There is a facility to check if a known file is open or not then you could choose to bypass this file and store its name to a string. Then at the end of the import routine if any files were missed it can report on the ones missed.

An alternative is to create a new blank database and link all the Excel spreadsheets within it. Then in vba trawl through the linked spreadsheets and import them via Access. This way you will bypass the issue you have.

This will only work if you know the names of the spreadsheets. If your VBA skills are up to it you could get it to create links in a known path to all spreadsheets encountered and use this as your base.

David


David

 

[gemma-the-husky]

just handle the error

on error got errorhandler

an open file returns an error number - not sure which off hand - but you can use this, and wait, or skip it etc etc

-------
the other thing is - you dont need to instantiate xl at all - transfer spreadsheet works without that.

 

[DCrake]

Dave
The error no = 70 - Share violation. I tried to use FileCopy to copy an open file and got the error. Using a wait could be tedious especially if the user has left it open and gone for lunch.

David

 

[gemma-the-husky]

Dave
The error no = 70 - Share violation. I tried to use FileCopy to copy an open file and got the error. Using a wait could be tedious especially if the user has left it open and gone for lunch.

David

i said either

you could ask user whether he wants to try again - let him find who has the file open, or skip it. the problem is if you skip loads of files without knowing, your data is likely to be compromised. at the very least save a file listing of the files that wouldnt load

 

[DCrake]

This is why I offered alternative solutions. I think the link spreasheets option would be the best as you do not need to content with open sheets. The only issue is the format of the spreadsheet in the first instance.

This has not been commented upon.

David

 

[Cameron.Turner]

Hi David and Dave,

Thanks for your excellent suggestions and feedback.

Unfortunately, I can't use suggestion #2 (linking spreadsheets) as the users create their own files each week and even though I have provided them a naming convention, they never follow it . Also, there are just too many sheets (circa 30 each week) being added. Linking them will take too much of my time each week.

As for Initiating excel, I do this so all the imports that failed actually open in excel (so I can see what files were READ ONLY).

Thank you both for the suggestion of creating an error report, but I already know which files fail (as they open during the import).

I realise the default position for Access is that doesn't want to import a file that's in use (as the data may change), but is there no way to override this and make Access import anyway?

Cam

 

[gemma-the-husky]

not directly - it produces a run time error - error 70 doesnt ring a bell, but im not sure -

although if it opens readonly, and you can see it - you can then copy and paste manually? - bit long winded though

i still think its easier to extend this, and store a table of all your file names - then you can record in there the load status etc, and keep trying to reload the ones that failed. doing it that way automates the process, and you also store a full historic record of how the stuff got loaded.

 

[Cameron.Turner]

Hi,

Unfortunately, this process has to run automatically. I am a contract consultant and won't be here in a few weeks. It needs to run by itself pretty much so I can't be setting up a process to link files.

I was thinking if there was an If statement I could use saying if the file was READ ONLY, then import anyway, If not, continue with code....

But I have no idea if that's possible or what that code would look like...

Cam