Solved

How to tackle  different Excel Versions on user machine.

Posted on 2003-10-24
16
183 Views
Last Modified: 2010-05-03
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
0
Comment
Question by:SKCD
  • 11
  • 5
16 Comments
 
LVL 16

Accepted Solution

by:
Richie_Simonetti earned 100 total points
ID: 9615943
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9615998
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
 

Author Comment

by:SKCD
ID: 9616207
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616335
You never said that error was "The instruction at '0x00000000' referenced memory at '0x00000000'" in first place!!!!
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616351
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616381
Also, you don't neet to .select and .activate nothing to work automating Office apps.
0
 

Author Comment

by:SKCD
ID: 9616455
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616572
As i said you, to me, the best approach is late binding.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616608
Also, try to debug with F8 to see where the error comes up.
0
 

Author Comment

by:SKCD
ID: 9616734
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9616861
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
 

Author Comment

by:SKCD
ID: 9616973
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9617373
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
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9617389
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
 

Author Comment

by:SKCD
ID: 9617525
Thanks a lot!!

You made my day.. It worked perfect.
Thanks a lot.
0
 
LVL 16

Expert Comment

by:Richie_Simonetti
ID: 9617561
Glad to help.
Thanks for "A" grade.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

937 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

3 Experts available now in Live!

Get 1:1 Help Now