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,
SKCD
SKCDAsked:
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.
0

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
     err.clear
    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

0
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
   
    Windows(spath).Activate
    With ActiveWorkbook
        Sheets("Contact Client").Select    'Sheet you want to copy
        Cells.Select
        Selection.Copy
    End With
    Range("A1").Select
   

    Workbooks.Open FileName:="C:\Book2.xls"
    Windows("Book2.xls").Activate
    With ActiveWorkbook

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

    End With
    Range("A1").Select
    ActiveWorkbook.Close True
    'ActiveWorkbook.Save
   
End Sub
 
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Richie_SimonettiIT OperationsCommented:
You never said that error was "The instruction at '0x00000000' referenced memory at '0x00000000'" in first place!!!!
0
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.
0
Richie_SimonettiIT OperationsCommented:
Also, you don't neet to .select and .activate nothing to work automating Office apps.
0
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.
0
Richie_SimonettiIT OperationsCommented:
As i said you, to me, the best approach is late binding.
0
Richie_SimonettiIT OperationsCommented:
Also, try to debug with F8 to see where the error comes up.
0
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..
0
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.
0
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..
0
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
        .Save
        .Close
    End With
    wbSource.Saved = True
    wbSource.Close
    Set wbDest = Nothing
    Set wbSource = Nothing
    .Quit
End With

Set xls = Nothing
End Sub
0
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.
Cheers
0
SKCDAuthor Commented:
Thanks a lot!!

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

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.