Convert spreadsheet to useable format with VBA

I have developed an application that will be distributed with MS Access 2013 Free Runtime. The app needs to import a spreadsheet that is generated by another application. Not sure what format it's in because it comes named with an xls extension and will open in Excel but when I save the file type is Web application. Nevertheless with the help of EE I have come up with the following code that will save it in a format that can then be imported. Problem is this code only seems to work with MS Excel 2010 and 2013. I am uncertain if it will run in 2007 but it definitely won't run on a PC loaded with Excel 2003. However Access 2003 will open the file. Does anyone know how I can change the code to be more universally functional?

        Set xlObj = CreateObject("excel.application")
        xlObj.Application.displayalerts = False
        On Error Resume Next
        xlObj.Workbooks.Open sSiteDataFilePath & sExcelFileName
        If Err.Number = 1004 Then 'this is not in the right format so ignore the file
            'loop to next file
            MsgBox "Your file was a non-conforming spreadsheet and has been moved to the SiteData folder " _
                & sSiteDataFilePath & sExcelFileName, , "Invalid Spreadsheet Found"
           
            xlObj.Quit
            Set xlObj = Nothing
            Exit Function
        Else
            xlObj.activeworkbook.saveas sSiteDataFilePath & sExcelFileName, FileFormat:=56
   
            xlObj.Quit
            Set xlObj = Nothing
endif
LVL 1
Rob4077Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
it definitely won't run on a PC loaded with Excel 2003
Can you specify what you mean by "won't run"? Do you mean the Access application shuts down? Or that the app runs, but the section you specify above doesn't do anything?

If it errors out, can you provide the error?

Most likely the Excel sheet was downloaded from the web, which is why the Import engine recognizes it as a "web application". I'm not sure you can convert those with versions prior to 2007. You might be able to work around this by installing the Compatibility Pack: https://support.microsoft.com/en-us/kb/924074.

Also be aware of this using the Runtime:

https://support.microsoft.com/en-us/kb/295179/en-us

Basically, the CreateObject method is not supported in the Runtime. It might work on some machines, depending on their specific environment, but it's not guaranteed to work on all machines with the Runtime. If you're distributing this "in the wild", you might consider a different platform (like .NET).
Rob4077Author Commented:
Thanks for your comments and references Scott.

The error trap is picking it up and the error message in the code is being generated. I will investigate the compatibility pack you mentioned to see if that will work.  

I was not aware that the CreateObject method is not supported in Runtime. The reference you gave says that relates to 2003 and earlier. Do you know if the same restriction apply to 2007 onwards?

There's a bit of a legacy to this app. It will be used by about 300 franchisees because their franchisor's system doesn't provide some functionality they want. I wanted to make it as seamless as possible so I even bought Sagekey so I could cause minimal issues. Now I am finding the CreateObject method may not work and Sagekey won't work on machines where the user has installed MS Access 64 bit so it's backfiring on me somewhat. I would love to develop it as a .NET app but I don't have the skill

The spreadsheet being installed opens perfectly in every version of Excel I've tried  but you have to specifically change the type to save it as a standard spreadsheet. Maybe I need to tell them to open it and save it properly before importing but that introduces a new level of inconvenience.
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's what I've always done with downloaded spreadsheets - instruct the users to open them, and then save them in the correct format. depending on frequency, that's the simplest "fix". If they have to do this once a day, then they're usually okay with it. If they're doing this every hour, you're probably going to get some squabble on this.

If you're just trying to get data from the file, you might be able to open it with ADO:

https://support.microsoft.com/en-us/kb/257819

The article above shows how to use the Extended Properties of an ADO Connection. I'm not sure if it would work with a downloaded Excel sheet, but it might be worth a try to avoid the issues you're facing now.

AFAIK, CreateObject has never been supported in the Runtime. If you look toward the end of that article, you'll see that 2007 is specifically mentioned. That's not to say you cannot do this, and I'm sure you'll hear "It works fine on my machine" from many different sources here, but the fact remains that it's an unsupported method, and therefore when you run into troubles (notice I did not say "if") you'll be out of luck.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Rob4077Author Commented:
Attached is a compressed, doctored sample copy of the spreadsheet that needs to be imported. Can someone help me determine if there's a different way of linking to it so I can read the data using a Runtime version of MS Access
030515.xls
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Did you try the ADO methods above?
Rob4077Author Commented:
Hi Scott, no I haven't tried the ADO methods yet - had a frantic week and ran out of time. However I considered your comment " If they have to do this once a day, then they're usually okay with it" and realise that's the easiest fix. They only have to do this once a week so they can live with it.

Thanks again for your help. I will try the ADO methods and if they work I will include them in the next upgrade.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.