Importing Errors but only when Excel spreadsheet is closed

[rlarkin]

Hi,

I'm importing an Excel spreadsheet, which has formulas that output either a value or a blank ("") into the cell. I've had problems getting Access to accept the blank cells with numbered and dated columns. I managed to get this to, sort of, work by using NA() instead of "" in the Excel formula. Access doesn't show NA() cells or seem to have issue with them.

This only works if the spreadsheet is open though, when it's closed it produces the same import errors as if they were all "" cells. Is there any way around this, as opening the spreadsheet, even with .visible = False, is very slow?

I hope there is, otherwise I get the feeling I'm going to have to code something ridiculous to get it to work.

Thanks,

Russ

 

[gemma-the-husky]

access is stricter than excel in respect of type checking

if you design a field in access and give it a type date, or number, then trying to import a space into that field (zero length string) is not permitted.

now if the field is set as required, then the whole row will be rejected.

MAYBE an n/a imports as null, but I am not sure.

I expect it's something like that going on.

 

[rlarkin]

It's a shame, as it works absolutely fine when open. Is there any chance there is an Excel formula code that will give a definite Null value? I could just delete the import errors, but it's bugging me.

 

[boblarson]

It's a shame, as it works absolutely fine when open.

Well, then open it first using

Dim objXL As Object
 
Set objXL = CreateObject("Excel.Application")
 
objXL.Workbooks.Open("YourFilePathAndFileNameHere")
 
' Do your importing routing here (or call it from here)
 
' then
 
objXL.ActiveWorkbook.Close False
objXL.Quit
 
Set objXL = Nothing

While you make sure it is working correctly you probably will want to also set

objXL.Visible = True

just so that if an error occurs you can just close the Excel App easily instead of having to go to Task Manager to kill the process. Once you have it working, just comment out that part and then the opening of it will remain hidden and people will never know you did that.

 

[rlarkin]

Hello Bob,

That's something I considered (I was actually forced to do that when the spreadsheet was linked), but I didn't want to cheap out and just open the spreadsheet if there was an actual way to fix the issue. I'm accessing the import sheet over a network, so it's not practical.

I'll keep looking for the time being, if I can't find another solution I'll just use a splash screen while it does the importing, or just delete the import errors.

Russ

 

[boblarson]

Hello Bob,

That's something I considered (I was actually forced to do that when the spreadsheet was linked), but I didn't want to cheap out and just open the spreadsheet if there was an actual way to fix the issue.

Doesn't sound to me like there is, because of the type of data.

I'm accessing the import sheet over a network, so it's not practical.

Umm, yes it is. What's the difference of opening it up and doing it than doing the import without opening it. You are still opening the spreadsheet, but in different ways.

 

[rlarkin]

If I just run the import and accept the errors, it does it in a fraction of the time, so I was hoping I could find a way to fix the errors, without resorting to opening the Excel spreadsheet beforehand.

I must admit I'm confused by your remark, and must be missing something, as opening the spreadsheet and importing, in this instance, takes longer than just importing with TransferSpreadsheet.

As you say, there doesn't appear to be a quick-fix solution to the data type issue, at least I haven't found one yet. I'm looking into importing everything as text, then changing the data type afterwards as a possible workaround. I'll update this thread if I find something that works.

Russ

 

[boblarson]

I must admit I'm confused by your remark, and must be missing something, as opening the spreadsheet and importing, in this instance, takes longer than just importing with TransferSpreadsheet.

Yes, you are missing something. By opening the spreadsheet you are letting it resolve the formulaic values before importing. By importing without opening it, it is erroring because it can't resolve those values. It is faster to just report errors than it is to resolve the values.

 

[rlarkin]

Thanks, Bob. The spreadsheet is opened to update it, but this isn't always inclusive to using the Access part, which currently only builds reports from the information. As such, the values will only change if it's been upated, and therefore opened.

I guess what I was looking for was a way to resolve the null values into something akin to regular values, so that it doesn't error when it's importing closed. Resolving to "" would work if they weren't restricted data types, and NA() doesn't work for the reason you mentioned. I'll keep looking.