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.applic
xlObj.Application.displaya
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.savea
xlObj.Quit
Set xlObj = Nothing
endif
Set xlObj = CreateObject("excel.applic
xlObj.Application.displaya
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.savea
xlObj.Quit
Set xlObj = Nothing
endif
ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
030515.xls
Did you try the ADO methods above?
ASKER
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.
Thanks again for your help. I will try the ADO methods and if they work I will include them in the next upgrade.
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).