Solved

How to tackle  different Excel Versions on user machine.

Posted on 2003-10-24
16
182 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

758 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

21 Experts available now in Live!

Get 1:1 Help Now