Last column left off Spreadsheet import (1 Viewer)

[Jarobe86]

Hey guys I think I may have a tough question

So I query data from our SQL db with sap business objects infoview and it spits out an excel file. I have aN access db with a macro that pulls the excel data into a table with docmd transfer spreadsheet. (used macro builder.)

The problem is: It will leave the last column blank in the access table unless I manually (or use vb) open and save the excel file before i run the access macro.


We are all scratching our heads with this one.

Things we verified/tried

The range was left blank
adding a dummy column (works until someone opens the excel file)

Maybe something to do with versions of excel? We figure something must be happening behind the scenes when we resale that allows the macro to work.

Thanks to anyone who might be able to help!!!!

 

[Galaxiom]

Sounds like SAP is not constructing the file correctly. I'm only guessing but I expect the column count property of the spreadsheet is written somewhere in the file and SAP is writing the wrong value there where it is read by Access.

When you save the file the correct value is recalculated from the data.

If it is an xlsx file the components are stored as xml files all zipped together. These components can be viewed by opening the file with a program like 7-Zip and looking directly at the xml. You might be able to spot the wrong value by comparing the before and after saved versions.

BTW Word docx files are the same. Before I learnt to use the VBA in Word I once changed all the links in a folder full of Word files by doing a Find and Replace on the components after unzippiing them.

 

[Jarobe86]

Thank you for your reply. I agree with you. We couldn't even get the last column to update when we tried to import the excel file with the access excel import wizard. The documents are 97-2003 so I don't think xlsx is an issue, but I compared the excel document that I had opened and saved with the one that I left alone and noticed a .2mb difference. I don't think the file name change would be that different. I guess SAP has issues with live data? We weren't able to export a csv file with a schedule, it would fail unless we did saved each file individually from business objects.

At this point we will schedule a task to run the macro to open and close all of our excel files.

Thanks again!!

 

[Galaxiom]

SAP is involved in a huge and expensive debarcle here in Australia where they tried to adapt one of their database products to something it was never designed to do.

The Queensland Government Health Department Salary system overran its budget by many millions of dollars and resulted in a system that didn't work with many staff not being paid for a considerable time. Others were overpaid and a fortune has been lost because of the cost of recovering these amounts.

Essentially the problem was that they took a payroll system designed for 9 to 5 workers and tried to adapt it to the widely variable hours in hospitals. It was a disaster and several heads rolled over it.

The budget to fix the problems is over $200 million.

Of course the decision makers at the health Department should shoulder the main responsibility but I also blame SAP for not being able to determine their product was unsuitable in the first place and failing to guide how it might be adapated in a sensiblly structured way. I wouldn't be recommending them for anything.

 

[Jarobe86]

That is wild!

So we solved the issue. There was an error with the auto-width. It was set to "yes" but the checkbox it was associated with was unchecked. We had to manually resize the last column to enable this checkbox to work correctly. Not only did we have to do this, but we had to set a prefrence to emphasize formats when exporting to excel. We had to both of these in conjunction for it to work. Just doing one did not solve the issue.

We are hoping to get direct access to our SQL server, or at least a view, so we can bypass sap business objects


Thanks