Question Import from excel workbook only if worksheet exists

[Romio68]

Due to a transition between two versions of the database, in need to check if a worksheet exists in a workbook before import.
I use DoCmd.TransferSpreadsheet Method to import data. If the worksheet is not present, access doesn't throw any error, but hang on the instruction related to the missing worksheet.
What I want to do is to execute the instruction only if there is a worksheet to import, else skip.
Also, I want to achieve this without importing additional libraries in access, since the database must run in runtime mode as well.

Thank You for helping me.
http://msdn.microsoft.com/en-us/library/office/ff844793(v=office.15).aspx

 

[Uncle Gizmo]

I can't speak for this particular problem.

But in a general sense, the problem is usually that you are trying to use an object that does not exist, hence it crashes.

The general solution is to get the objects NAME.

So you need to write some code:-

That opens the spreadsheet, (not sure if you need to open it)
Loop through the worksheet collection, retrieve each worksheets name and compare it to the name you are looking for.

The code won't break because you are only looking for the text name of the object.

 

[Romio68]

The problem comes from that "open workbook" part. In order to use this approach, the user must have office installed and also must have the proper version of excel.
The database is running in runtime mode, in order to avoid any Access version conflicts and to be able to import/export data without having a certain Office installed.
The .xlsx file exported will be correctly generated even if there is no Excel installed.
The file itself is exported for syncing with other databases.
Import as well, will be not Office dependent, Access runtime being able to handle it.
If I try to use any option where the workbook have to be opened prior to be manipulated, then i get in the Office existence/version pitfall.

 

[Uncle Gizmo]

>>>If I try to use any option where the workbook have to be opened<<<

Looking at the Workbook collection shouldn't open the workbooks. The collection will return the names of the workbooks in it, loop through the names and compare against the name of your workbook. If your workbook name can't be found, then abort, don't try and open the workbook.

 

[Romio68]

Don't have any idea how to achieve this without opening the wb...
early or late binding are both opening the object, yet I don't know if any of these processes are possible without having excel installed on the machine (as I told you, there is a slight possibility that the user don't have office installed)

 

[Uncle Gizmo]

Don't have any idea how to achieve this without opening the wb...
early or late binding are both opening the object, yet I don't know if any of these processes are possible without having excel installed on the machine (as I told you, there is a slight possibility that the user don't have office installed)

Well the short answer is I don't know enough about it!

Another approach:-

You say:-
>>>If the worksheet is not present, access doesn't throw any error, <<<

Are you sure it's not generating an error code?

Do you have error checking in your routine?

 

[Romio68]

yes... checked
No error handler that may mask the message
DoCmd.SetWarnings True

Set some progress beeps to check where i loose control

The SOB is hanging on the DoCmd.Transfersheet that contains the name of the missing spreadshett...

 

[Uncle Gizmo]

I'm thinking you should add an error handler.

 

[CJ_London]

So I think you are saying that you are not sure you can use early or late binding (latter preferred) if Office is not installed? and you have no means of testing it, i.e. find a machine without office and try it?

If that is the case - these links may help

http://amiraryani.wordpress.com/200...nd-access-files-without-installing-ms-office/


http://stackoverflow.com/questions/...-excel-on-a-machine-without-installed-ms-offi

Not sure it provides all the answers and you may need a bit of a rewrite

 

[Romio68]

CJ, both links indicates that Excel must be installed in order to use the component...

 

[Romio68]

I'm thinking you should add an error handler.

I should, but what error to trap since none is thrown up by access :banghead:...

If i get the tiniest error message or behavior other then the stall, the problem is solved. But I have none...

 

[Uncle Gizmo]

I should, but what error to trap since none is thrown up by access :banghead:...

If i get the tiniest error message or behavior other then the stall, the problem is solved. But I have none...

I'd still try to catch the error message your self. It is a simple piece of code well worth a try..

 

[CJ_London]

I think you need to read deeper - here is another link

http://forums.asp.net/t/1557837.aspx?How+to+add+Excel+without+installing+office

And if your conclusion is that you can't open an excel file if excel is not installed, suggest rather than trying to trap an error when opening it, look to see if excel is actually installed when you application is opened and then disable the relevant forms/processes if not found.

 

[Romio68]

On a second thought

I deliver the application as .accdr in order to give access to all users without having them forced to install Ms Access. Anyway, they must (at least) download and install Access Runtime.
Now...
Is there a component or something that may be embedded in the accdr file, or having it installed as the Runtime is installed, that may allow me to integrate the excel components in the .accdr file without having Office installed? (of course, it must be license free)

If there is such a thing, then I may use the excel component to read inside the excel object, without forcing the user to install Office,

 

[CJ_London]

providing you are using the installer, you can install office.interop as part of that process