Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Red face Unanswered: Access VBA copy paste in Excel

    Hi

    Im having problmes with copy paste in excel when using vba code in access. Either new rows dont contain the correct formulas or a application/object error is given. Any ideas please....

    I have an access application that has been converted to 2003, it generates a report by inserting one record at a time into excel. A second piece of code then inserts a new row in the excel and copies the row to the new row so the data scrolls down the screen.

    The rows contain formulas and values. Ive tried the following code but all seem to have their own problems

    'XLXWorksheet.Rows(lRow + iOffset) = XLXWorksheet.Rows(lRow).Value 'dosnt copy formulas

    'XLXWorksheet.Rows(lRow + iOffset) = XLXWorksheet.Rows(lRow).Formula 'dosnt update relative formula cell refs so formulas useless

    'XLXWorksheet.Range(srcStart, srcEnd).Copy
    'XLXWorksheet.Paste Destination:=XLXWorksheet.Range(destStart, destEnd) 'app/obj defined error after 4000 rows

    I have a feeling that the copy paste solution fails because the clipboard cant cope with the ammount of data, please let me know if you know any possible causes/solutions, thanks

  2. #2
    Join Date
    Mar 2006
    Posts
    163
    As far as I can see you have the wrong syntax for the copy paste.

    Also it's hard to tell why your code is failing without knowing the values of the variables.

  3. #3
    Join Date
    Jun 2006
    Posts
    3
    Thanks,

    The xls data is various character, number and currency values and xls formula, sorry but I cant post an example.

    Please can you let me know what is wrong with the syntax as this is from a MS example.

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't if this is much help but for Excel code I tend to record a macro of me performing the action and then tidy the code up.

    BTW - I think Norie meant the value of the variables (e.g. destStart) not the cells.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Jun 2006
    Posts
    3
    thanks, nice suggestion, ill have a try.
    Any other ideas please let me know

    p.s. The destStart etc are strings containing the cell reffs i.e. A17, BF17.

  6. #6
    Join Date
    Mar 2006
    Posts
    163
    Eh, I didn't ask for any examples/data.
    Code:
    XLXWorksheet.Range(srcStart, srcEnd).Copy Destination:=XLXWorksheet.Range(destStart, destEnd)

  7. #7
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Just my tuppence worth,

    This will copy values including turning formulars to values
    XLXWorksheet.Rows(lRow).Copy
    XLXWorksheet.Rows(lRow + iOffset).PasteSpecial Paste:=xlValues

    This will copy cell contents (valus as values, formulars as formulas with relational as relational and absolute as absolute or any mixture).
    XLXWorksheet.Rows(lRow).Copy
    XLXWorksheet.Rows(lRow + iOffset).PasteSpecial Paste:=xlFormulas

    With regards to the last Item, without knowing the range of address involved, I can only comment that you only need to specifythe anchor address for the destination. ie
    XLXWorksheet.Range(srcStart, srcEnd).Copy
    XLXWorksheet.Paste Destination:=XLXWorksheet.Range(destStart)

    I don't know if that helps ?

    Excel is quite flexible and you can use this syntax or that suggested by norie

    Without resorting to R1C1 notation and copying cells individually I do not know if you can COPY a relation formular and retain it's absolute address, if that is what you are trying to do ?


    MTB

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •