How to tackle different Excel Versions on user machine.

A part of my application needs to read an Excel file whch  is password protected. I am using ADODB connection to read the Excel file although the file needs to be open at the time when the program is running otherwise I will get an error. Then I need to copy this Excel file to another Excel file which is not password protected and this won't require any passwords.
My problem is that I have to instantiate an excel object to create an excel application when I have to copy. My development machine has the Excel Object 10.0 Reference Library. When I compile my program on my development machine and run on other m/c which has lower versions of Excel, it does not find the reference library and the program fails.
I can copy one excel file to another using ADODB or Excel object.. the only problem being how to make the program compatible so that it runs for all versions of EXCEL object libraries??
Any of your suggestions would be welcome.

Thanks in advance,
Who is Participating?
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.

Richie_SimonettiIT OperationsCommented:
You need to use late binding, so:
dim xlapp as object

set xlapp=createobject("Excel.Application")
since you don't specify any version it should works.

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
Richie_SimonettiIT OperationsCommented:
Another possible way:

dim xlapp as object

on error resume next

set xlapp=createobject("excel.application.10")
if err>0 then
    set xlapp=createobject("excel.application.9")
    if err>0 then
         set xlapp=createobject("excel.application.8")
             if err>0 then  msgbox "Unable to find Excel app installed",vbexclamation,"Error"
    end if
end if

SKCDAuthor Commented:
I am using the following subroutine to copy one excel sheet to other. The purpose being just to remove the password protection on the newly created excel sheet. I am still getting the error "The instruction at '0x00000000' referenced memory at '0x00000000'. The memory could not be read"

Sub PasteValues(path As String)
Dim fso As New FileSystemObject
Dim spath As String

Dim xls As Object
Dim wb As Object

Set xls = CreateObject("Excel.Application")
xls.Visible = False
Set wb = xls.Workbooks.Add
wb.SaveAs "C:\Book2.xls"

wb.Close True

    spath = fso.GetFileName(path)
    Workbooks.Open FileName:=path
    With ActiveWorkbook
        Sheets("Contact Client").Select    'Sheet you want to copy
    End With

    Workbooks.Open FileName:="C:\Book2.xls"
    With ActiveWorkbook

        Sheets("Sheet1").Name = "New Name"
        Sheets("New Name").Select
        'Sheets("New Name").Paste

    End With
    ActiveWorkbook.Close True
End Sub
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Richie_SimonettiIT OperationsCommented:
You never said that error was "The instruction at '0x00000000' referenced memory at '0x00000000'" in first place!!!!
Richie_SimonettiIT OperationsCommented:
Are you working with VBA or VB?
If you are working with VB, some objects that you are using are not available.
Also, never trust in Activexxxxxxxx stuff, at least, not ouside VBA.
Richie_SimonettiIT OperationsCommented:
Also, you don't neet to .select and .activate nothing to work automating Office apps.
SKCDAuthor Commented:
I am sorry but that error came when I changed the code as you said.
I am working on VB.
What do you think should be the best way so that the application runs on all platforms without having to worry which Excel version is installed there.
Richie_SimonettiIT OperationsCommented:
As i said you, to me, the best approach is late binding.
Richie_SimonettiIT OperationsCommented:
Also, try to debug with F8 to see where the error comes up.
SKCDAuthor Commented:
Yes, it worked. I used the late binding. The only problem is that the format altogether changes in the sheet which is created after copying. Now the spreadsheet will have column lengths of fixed size where data is Autofit(wrapped). From the dropdowns in the original sheet, it only picks up the selected data.
Can it be somehow taken care of??

Thanks for all your help..
Richie_SimonettiIT OperationsCommented:
First, that's not part of original question (How to tackle  different Excel Versions on user machine)
Then, ...What do you mean by dropdowns (Combo box controls or data validation)?
if the last one, then you have to know that those values should be stored in someother place (even not in the same sheet!).
What i am thinking is after you copy the sheet, select the original range of cells, copy it and PasteSpecial in target (copied sheet) range to see if all is it ok.
SKCDAuthor Commented:
I'll add points for my tracking off teh original question.
When I was diong it previously without late binding the sheet was getting copied exactly the same. Now with late binding, when I do PasteSpecial, a message comes up saying that "Picture is too big, it will be truncated" and program gets terminated.
The original sheet uses data validation , the values being stored in itself only.

Thanks again..
Richie_SimonettiIT OperationsCommented:
Please, try this:

Sub PasteValues(path As String)

Dim xls As Object                           ' Application Object
Dim wbSource As Object, wbDest As Object    ' Workbook Object
Dim sh As Object                            'Sheet Object

Set xls = CreateObject("Excel.Application")
With xls
    .Visible = True
    Set wbDest = xls.Workbooks.Add
    wbDest.SaveAs "C:\Libro2.xls"
    Set wbSource = .Workbooks.Open(path)
    wbSource.Sheets("Hoja1").Copy After:=wbDest.Sheets(2)  'change name of sheet accordingly
    With wbDest
    End With
    wbSource.Saved = True
    Set wbDest = Nothing
    Set wbSource = Nothing
End With

Set xls = Nothing
End Sub
Richie_SimonettiIT OperationsCommented:
Dim sh As Object                            'Sheet Object

This is not required, delete the line.
Please, we crossed postings, let me know how it works.
SKCDAuthor Commented:
Thanks a lot!!

You made my day.. It worked perfect.
Thanks a lot.
Richie_SimonettiIT OperationsCommented:
Glad to help.
Thanks for "A" grade.
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
Visual Basic Classic

From novice to tech pro — start learning today.