DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 8 of 8

Thread: Error when calling PasteSpecial in French Excel from VB

  1. #1
    Mark Alexander Bertenshaw Guest

    Error when calling PasteSpecial in French Excel from VB


    Hi!

    I have been recently fixing the Office Automation class of our VB5 application,
    and it is almost ready; except that I have just accidentally discovered that
    I get an error raised with the French Version of Excel 97 when I try to call
    the PasteSpecial method of a worksheet:

    Runtime error 1004, Application-defined or object-defined error:
    La méthode PasteSpecial de la classe Worksheet a échoué.

    At a guess, this is the same as "The method PasteSpecial of the class Worksheet
    could not be found". When I got this, I looked through the code, and discovered
    that certain sections were actually late bound. I deliberately went through
    the code, and replaced all late bound calls with early bound calls, creating
    intermediate typed object variables where necessary. The whole project used
    the Excel 5 type library.

    Unfortunately, I have compiled again, and this time it looks as if nothing
    has changed! The error still occurs. Has anybody got any idea as to what
    to do to solve this problem?

    Thanks,

    -------------------------
    Mark Alexander Bertenshaw
    Programmer/Analyst
    Prime Response
    Brentford
    UK


  2. #2
    Paul Clement Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    On 4 Sep 2000 08:34:05 -0700, "Mark Alexander Bertenshaw" <mark.bertenshaw@virgin.net> wrote:

    ¤
    ¤ Hi!
    ¤
    ¤ I have been recently fixing the Office Automation class of our VB5 application,
    ¤ and it is almost ready; except that I have just accidentally discovered that
    ¤ I get an error raised with the French Version of Excel 97 when I try to call
    ¤ the PasteSpecial method of a worksheet:
    ¤
    ¤ Runtime error 1004, Application-defined or object-defined error:
    ¤ La méthode PasteSpecial de la classe Worksheet a échoué.
    ¤
    ¤ At a guess, this is the same as "The method PasteSpecial of the class Worksheet
    ¤ could not be found". When I got this, I looked through the code, and discovered
    ¤ that certain sections were actually late bound. I deliberately went through
    ¤ the code, and replaced all late bound calls with early bound calls, creating
    ¤ intermediate typed object variables where necessary. The whole project used
    ¤ the Excel 5 type library.
    ¤
    ¤ Unfortunately, I have compiled again, and this time it looks as if nothing
    ¤ has changed! The error still occurs. Has anybody got any idea as to what
    ¤ to do to solve this problem?

    What does the line of code that calls this method look like?


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  3. #3
    Mark Alexander Bertenshaw Guest

    Re: Error when calling PasteSpecial in French Excel from VB


    Paul -

    Thanks for replying:

    Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    >
    >What does the line of code that calls this method look like?


    To put it into context, I'll give you the whole procedure:

    -------------------------------------------------------------


    Public Sub CopyCSVToExcel(ByRef sValue As String)
    ' Purpose: Copies the contents of a grid to a new Excel workbook.
    ' Inputs: grd A reference to a grid Control
    ' Outputs: <None>
    ' In/Outs: <None>
    ' Notes: Most code was moved from CopyGridToExcel.
    ' Changed: 04/09/2000: MAB:
    ' Now everything is Early Bound, so that we don't go via crazy
    non-english type libraries.
    Const MEMBER_NAME As String = "CopyCSVToExcel"
    Dim oExcelApp As Excel.Application
    Dim oExcelBooks As Excel.Workbooks
    Dim oExcelBook As Excel.Workbook
    Dim oExcelSheet As Excel.Worksheet
    Dim oRange As Excel.Range

    goASEh.AddRoutineToFailSafe MODULE_NAME, MEMBER_NAME
    On Error GoTo Error_Handler

    ' Clear, and copy the text onto the clipboard.
    With Clipboard
    .Clear
    .SetText sValue, vbCFText
    End With

    ' Get an instance of Excel (if it's not there, it's created).
    Set oExcelApp = GetExcelApplication(5) ' 04/09/2000: MAB: Changed
    back to 5.

    ' Create a new workbook.
    Set oExcelBooks = oExcelApp.Workbooks
    Set oExcelBook = oExcelBooks.Add

    ' Paste the text on the clipboard in the current worksheet.
    GDebug "Get ActiveSheet"
    Set oExcelSheet = oExcelBook.ActiveSheet
    GDebug "Pasting Text"
    oExcelSheet.PasteSpecial "Text"

    ' Format this range using the auto-format feature.
    Set oRange = oExcelApp.Selection
    oRange.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, _
    Font:=True, Alignment:=True, Border:=True, _
    Pattern:=True, Width:=True

    ' Set the selection back to the first (top left) cell.
    Set oRange = oExcelSheet.Cells(1, 1)
    oRange.Select

    ' Make excel visible, and maximise it.
    oExcelApp.Visible = True
    oExcelApp.WindowState = xlMaximized

    goASEh.RemoveRoutineFromFailSafe MODULE_NAME, MEMBER_NAME

    Exit Sub

    Error_Handler:

    ' Changed: 07/06/2000: MAB:
    ' Must be handled in the client.
    Select Case Err.Number
    Case 1004
    RaiseError easAutomationError, MODULE_NAME, CStr(Err.Number) & ":
    " & vbNewLine & Err.Description '
    Case Else
    RaiseError Err.Number, MODULE_NAME, Err.Description
    End Select

    End Sub

    -------------------------------------------------------------


    -------------------------
    Mark Alexander Bertenshaw
    Programmer/Analyst
    Prime Response
    Brentford
    UK


  4. #4
    Paul Clement Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    On 5 Sep 2000 09:54:50 -0700, "Mark Alexander Bertenshaw" <mark.bertenshaw@virgin.net> wrote:


    ¤
    ¤ Public Sub CopyCSVToExcel(ByRef sValue As String)
    ¤ ' Purpose: Copies the contents of a grid to a new Excel workbook.
    ¤ ' Inputs: grd A reference to a grid Control
    ¤ ' Outputs: <None>
    ¤ ' In/Outs: <None>
    ¤ ' Notes: Most code was moved from CopyGridToExcel.
    ¤ ' Changed: 04/09/2000: MAB:
    ¤ ' Now everything is Early Bound, so that we don't go via crazy
    ¤ non-english type libraries.
    ¤ Const MEMBER_NAME As String = "CopyCSVToExcel"
    ¤ Dim oExcelApp As Excel.Application
    ¤ Dim oExcelBooks As Excel.Workbooks
    ¤ Dim oExcelBook As Excel.Workbook
    ¤ Dim oExcelSheet As Excel.Worksheet
    ¤ Dim oRange As Excel.Range
    ¤
    ¤ goASEh.AddRoutineToFailSafe MODULE_NAME, MEMBER_NAME
    ¤ On Error GoTo Error_Handler
    ¤
    ¤ ' Clear, and copy the text onto the clipboard.
    ¤ With Clipboard
    ¤ .Clear
    ¤ .SetText sValue, vbCFText
    ¤ End With
    ¤
    ¤ ' Get an instance of Excel (if it's not there, it's created).
    ¤ Set oExcelApp = GetExcelApplication(5) ' 04/09/2000: MAB: Changed
    ¤ back to 5.
    ¤
    ¤ ' Create a new workbook.
    ¤ Set oExcelBooks = oExcelApp.Workbooks
    ¤ Set oExcelBook = oExcelBooks.Add
    ¤
    ¤ ' Paste the text on the clipboard in the current worksheet.
    ¤ GDebug "Get ActiveSheet"
    ¤ Set oExcelSheet = oExcelBook.ActiveSheet
    ¤ GDebug "Pasting Text"
    ¤ oExcelSheet.PasteSpecial "Text"
    ¤
    ¤ ' Format this range using the auto-format feature.
    ¤ Set oRange = oExcelApp.Selection
    ¤ oRange.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, _
    ¤ Font:=True, Alignment:=True, Border:=True, _
    ¤ Pattern:=True, Width:=True
    ¤
    ¤ ' Set the selection back to the first (top left) cell.
    ¤ Set oRange = oExcelSheet.Cells(1, 1)
    ¤ oRange.Select
    ¤
    ¤ ' Make excel visible, and maximise it.
    ¤ oExcelApp.Visible = True
    ¤ oExcelApp.WindowState = xlMaximized
    ¤
    ¤ goASEh.RemoveRoutineFromFailSafe MODULE_NAME, MEMBER_NAME
    ¤
    ¤ Exit Sub
    ¤
    ¤ Error_Handler:
    ¤
    ¤ ' Changed: 07/06/2000: MAB:
    ¤ ' Must be handled in the client.
    ¤ Select Case Err.Number
    ¤ Case 1004
    ¤ RaiseError easAutomationError, MODULE_NAME, CStr(Err.Number) & ":
    ¤ " & vbNewLine & Err.Description '
    ¤ Case Else
    ¤ RaiseError Err.Number, MODULE_NAME, Err.Description
    ¤ End Select
    ¤
    ¤ End Sub

    Has the destination of the paste been selected? What happens if you omit the Format argument
    ("Text") from the method call?


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

  5. #5
    Ian Williams Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    Hi Mark

    I'm not sure if this is of any help but I think your problem might be due to
    the fact you're programming in English.

    I've never used Excel95 but I see its object library is called XL5en32.olb,
    now with Excel5 its object library is XLen50.olb and this is specific to
    English, with Danish Excel 5 it's XLda50.olb - I use this a lot, and I
    assume the French one is called XLfr50.olb.

    To get Danish users to be able to use my Add-In's functions I had to get
    them to change their International setting in Tools | Options | Module
    General to English.

    regards

    Ian

    ** invalid email address, change dk to denmark

    homepage http://www.kingsoft-denmark.com/
    Tips & Tricks page http://tips.kingsoft-denmark.com/

    Mark Alexander Bertenshaw <mark.bertenshaw@virgin.net> wrote in message
    news:39b5255a$1@news.devx.com...
    >
    > Paul -
    >
    > Thanks for replying:
    >
    > Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    > >
    > >What does the line of code that calls this method look like?

    >
    > To put it into context, I'll give you the whole procedure:
    >
    > -------------------------------------------------------------
    >
    >
    > Public Sub CopyCSVToExcel(ByRef sValue As String)
    > ' Purpose: Copies the contents of a grid to a new Excel workbook.
    > ' Inputs: grd A reference to a grid Control
    > ' Outputs: <None>
    > ' In/Outs: <None>
    > ' Notes: Most code was moved from CopyGridToExcel.
    > ' Changed: 04/09/2000: MAB:
    > ' Now everything is Early Bound, so that we don't go via crazy
    > non-english type libraries.
    > Const MEMBER_NAME As String = "CopyCSVToExcel"
    > Dim oExcelApp As Excel.Application
    > Dim oExcelBooks As Excel.Workbooks
    > Dim oExcelBook As Excel.Workbook
    > Dim oExcelSheet As Excel.Worksheet
    > Dim oRange As Excel.Range
    >
    > goASEh.AddRoutineToFailSafe MODULE_NAME, MEMBER_NAME
    > On Error GoTo Error_Handler
    >
    > ' Clear, and copy the text onto the clipboard.
    > With Clipboard
    > .Clear
    > .SetText sValue, vbCFText
    > End With
    >
    > ' Get an instance of Excel (if it's not there, it's created).
    > Set oExcelApp = GetExcelApplication(5) ' 04/09/2000: MAB:

    Changed
    > back to 5.
    >
    > ' Create a new workbook.
    > Set oExcelBooks = oExcelApp.Workbooks
    > Set oExcelBook = oExcelBooks.Add
    >
    > ' Paste the text on the clipboard in the current worksheet.
    > GDebug "Get ActiveSheet"
    > Set oExcelSheet = oExcelBook.ActiveSheet
    > GDebug "Pasting Text"
    > oExcelSheet.PasteSpecial "Text"
    >
    > ' Format this range using the auto-format feature.
    > Set oRange = oExcelApp.Selection
    > oRange.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, _
    > Font:=True, Alignment:=True, Border:=True, _
    > Pattern:=True, Width:=True
    >
    > ' Set the selection back to the first (top left) cell.
    > Set oRange = oExcelSheet.Cells(1, 1)
    > oRange.Select
    >
    > ' Make excel visible, and maximise it.
    > oExcelApp.Visible = True
    > oExcelApp.WindowState = xlMaximized
    >
    > goASEh.RemoveRoutineFromFailSafe MODULE_NAME, MEMBER_NAME
    >
    > Exit Sub
    >
    > Error_Handler:
    >
    > ' Changed: 07/06/2000: MAB:
    > ' Must be handled in the client.
    > Select Case Err.Number
    > Case 1004
    > RaiseError easAutomationError, MODULE_NAME, CStr(Err.Number) & ":
    > " & vbNewLine & Err.Description '
    > Case Else
    > RaiseError Err.Number, MODULE_NAME, Err.Description
    > End Select
    >
    > End Sub
    >
    > -------------------------------------------------------------
    >
    >
    > -------------------------
    > Mark Alexander Bertenshaw
    > Programmer/Analyst
    > Prime Response
    > Brentford
    > UK
    >






  6. #6
    Mark Alexander Bertenshaw Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    Ian -

    Whilst not trying to be facetious, I have to truly say that I have only been
    programming in VB5. OK - I have had a reference to the English type
    library. I have strictly enforced early binding so that when the
    PasteSpecial method is compiled into the executeable, it is stored as , say,
    method id 21, rather than method PasteSpecial. I would absolutely agree
    that if I executed the latter on a machine with French Excel, it would look
    for a method in the type library called "PasteSpecial",. and fail since this
    doesn't exist in the type library, However, its equivalent method id 21 in
    the type library <does> exist, so it should work.

    --
    Mark Alexander Bertenshaw
    Programmer/Analyst
    PrimeResponse
    Brentford
    UK
    "Ian Williams" <advice@kingsoft-dk.com> wrote in message
    news:39b8a280@news.devx.com...
    > Hi Mark
    >
    > I'm not sure if this is of any help but I think your problem might be due

    to
    > the fact you're programming in English.
    >
    > I've never used Excel95 but I see its object library is called

    XL5en32.olb,
    > now with Excel5 its object library is XLen50.olb and this is specific to
    > English, with Danish Excel 5 it's XLda50.olb - I use this a lot, and I
    > assume the French one is called XLfr50.olb.
    >
    > To get Danish users to be able to use my Add-In's functions I had to get
    > them to change their International setting in Tools | Options | Module
    > General to English.
    >
    > regards
    >
    > Ian
    >
    > ** invalid email address, change dk to denmark
    >
    > homepage http://www.kingsoft-denmark.com/
    > Tips & Tricks page http://tips.kingsoft-denmark.com/
    >
    > Mark Alexander Bertenshaw <mark.bertenshaw@virgin.net> wrote in message
    > news:39b5255a$1@news.devx.com...
    > >
    > > Paul -
    > >
    > > Thanks for replying:
    > >
    > > Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote:
    > > >
    > > >What does the line of code that calls this method look like?

    > >
    > > To put it into context, I'll give you the whole procedure:
    > >
    > > -------------------------------------------------------------
    > >
    > >
    > > Public Sub CopyCSVToExcel(ByRef sValue As String)
    > > ' Purpose: Copies the contents of a grid to a new Excel workbook.
    > > ' Inputs: grd A reference to a grid Control
    > > ' Outputs: <None>
    > > ' In/Outs: <None>
    > > ' Notes: Most code was moved from CopyGridToExcel.
    > > ' Changed: 04/09/2000: MAB:
    > > ' Now everything is Early Bound, so that we don't go via crazy
    > > non-english type libraries.
    > > Const MEMBER_NAME As String = "CopyCSVToExcel"
    > > Dim oExcelApp As Excel.Application
    > > Dim oExcelBooks As Excel.Workbooks
    > > Dim oExcelBook As Excel.Workbook
    > > Dim oExcelSheet As Excel.Worksheet
    > > Dim oRange As Excel.Range
    > >
    > > goASEh.AddRoutineToFailSafe MODULE_NAME, MEMBER_NAME
    > > On Error GoTo Error_Handler
    > >
    > > ' Clear, and copy the text onto the clipboard.
    > > With Clipboard
    > > .Clear
    > > .SetText sValue, vbCFText
    > > End With
    > >
    > > ' Get an instance of Excel (if it's not there, it's created).
    > > Set oExcelApp = GetExcelApplication(5) ' 04/09/2000: MAB:

    > Changed
    > > back to 5.
    > >
    > > ' Create a new workbook.
    > > Set oExcelBooks = oExcelApp.Workbooks
    > > Set oExcelBook = oExcelBooks.Add
    > >
    > > ' Paste the text on the clipboard in the current worksheet.
    > > GDebug "Get ActiveSheet"
    > > Set oExcelSheet = oExcelBook.ActiveSheet
    > > GDebug "Pasting Text"
    > > oExcelSheet.PasteSpecial "Text"
    > >
    > > ' Format this range using the auto-format feature.
    > > Set oRange = oExcelApp.Selection
    > > oRange.AutoFormat Format:=xlRangeAutoFormatSimple, Number:=True, _
    > > Font:=True, Alignment:=True, Border:=True, _
    > > Pattern:=True, Width:=True
    > >
    > > ' Set the selection back to the first (top left) cell.
    > > Set oRange = oExcelSheet.Cells(1, 1)
    > > oRange.Select
    > >
    > > ' Make excel visible, and maximise it.
    > > oExcelApp.Visible = True
    > > oExcelApp.WindowState = xlMaximized
    > >
    > > goASEh.RemoveRoutineFromFailSafe MODULE_NAME, MEMBER_NAME
    > >
    > > Exit Sub
    > >
    > > Error_Handler:
    > >
    > > ' Changed: 07/06/2000: MAB:
    > > ' Must be handled in the client.
    > > Select Case Err.Number
    > > Case 1004
    > > RaiseError easAutomationError, MODULE_NAME, CStr(Err.Number) &

    ":
    > > " & vbNewLine & Err.Description '
    > > Case Else
    > > RaiseError Err.Number, MODULE_NAME, Err.Description
    > > End Select
    > >
    > > End Sub
    > >
    > > -------------------------------------------------------------
    > >
    > >
    > > -------------------------
    > > Mark Alexander Bertenshaw
    > > Programmer/Analyst
    > > Prime Response
    > > Brentford
    > > UK
    > >

    >
    >
    >
    >




  7. #7
    Mark Alexander Bertenshaw Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    Good news!

    My colleague has come back from holiday, and translates:

    "La méthode PasteSpecial de la classe Worksheet a échoué."

    as meaning:

    "The method PasteSpecial of the class Worksheet failed"

    which puts things in a lot better light. The early binding worked!!
    So why did it fail? Well, there appeared to be no differences between the
    way English Excel and French Excel was set up, so it had to be my code. I
    looked at the list of french formats in the "PasteSpecial" dialogue box, and
    it appears that the correct format name was "Texte" rather than "Text".
    This was rather horrifying, since it potentially meant that I would have to
    provide a non-English word for each language. I looked again at the
    documentation for PasteSpecial, and whilst it was <very> specific that this
    should be a String, it was actually typed as Variant. I took a guess and
    used the vbCfText integer constant (aka CF_TEXT) = 1, and lo! It worked.

    So there you have it.

    --
    Mark Alexander Bertenshaw
    Programmer/Analyst
    PrimeResponse
    Brentford
    UK

    "Mark Alexander Bertenshaw" <mark.bertenshaw@virgin.net> wrote in message
    news:39b3c0ed$1@news.devx.com...
    >
    > Hi!
    >
    > I have been recently fixing the Office Automation class of our VB5

    application,
    > and it is almost ready; except that I have just accidentally discovered

    that
    > I get an error raised with the French Version of Excel 97 when I try to

    call
    > the PasteSpecial method of a worksheet:
    >
    > Runtime error 1004, Application-defined or object-defined error:
    > La méthode PasteSpecial de la classe Worksheet a échoué.
    >
    > At a guess, this is the same as "The method PasteSpecial of the class

    Worksheet
    > could not be found". When I got this, I looked through the code, and

    discovered
    > that certain sections were actually late bound. I deliberately went

    through
    > the code, and replaced all late bound calls with early bound calls,

    creating
    > intermediate typed object variables where necessary. The whole project

    used
    > the Excel 5 type library.
    >
    > Unfortunately, I have compiled again, and this time it looks as if nothing
    > has changed! The error still occurs. Has anybody got any idea as to what
    > to do to solve this problem?
    >
    > Thanks,
    >
    > -------------------------
    > Mark Alexander Bertenshaw
    > Programmer/Analyst
    > Prime Response
    > Brentford
    > UK
    >




  8. #8
    Paul Clement Guest

    Re: Error when calling PasteSpecial in French Excel from VB

    On Mon, 11 Sep 2000 20:16:00 +0100, "Mark Alexander Bertenshaw" <mark.bertenshaw@virgin.net> wrote:

    ¤ Good news!
    ¤
    ¤ My colleague has come back from holiday, and translates:
    ¤
    ¤ "La méthode PasteSpecial de la classe Worksheet a échoué."
    ¤
    ¤ as meaning:
    ¤
    ¤ "The method PasteSpecial of the class Worksheet failed"
    ¤
    ¤ which puts things in a lot better light. The early binding worked!!
    ¤ So why did it fail? Well, there appeared to be no differences between the
    ¤ way English Excel and French Excel was set up, so it had to be my code. I
    ¤ looked at the list of french formats in the "PasteSpecial" dialogue box, and
    ¤ it appears that the correct format name was "Texte" rather than "Text".
    ¤ This was rather horrifying, since it potentially meant that I would have to
    ¤ provide a non-English word for each language. I looked again at the
    ¤ documentation for PasteSpecial, and whilst it was <very> specific that this
    ¤ should be a String, it was actually typed as Variant. I took a guess and
    ¤ used the vbCfText integer constant (aka CF_TEXT) = 1, and lo! It worked.

    I had a sneaky suspicion about that which is why I asked what happened when the argument was
    omitted. Unfortunately the documentation for this method isn't particularly complete.


    Paul ~~~ pclement@ameritech.net
    Microsoft MVP (Visual Basic)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center