Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 192
  • Last Modified:

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
0
SKCD
Asked:
SKCD
  • 11
  • 5
1 Solution
 
Richie_SimonettiCommented:
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
 
Richie_SimonettiCommented:
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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
Richie_SimonettiCommented:
You never said that error was "The instruction at '0x00000000' referenced memory at '0x00000000'" in first place!!!!
0
 
Richie_SimonettiCommented:
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_SimonettiCommented:
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_SimonettiCommented:
As i said you, to me, the best approach is late binding.
0
 
Richie_SimonettiCommented:
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_SimonettiCommented:
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_SimonettiCommented:
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_SimonettiCommented:
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_SimonettiCommented:
Glad to help.
Thanks for "A" grade.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now