SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Excel Problem

  1. #1
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Excel Problem

    <font class="small">Code:</font><hr /><pre> Dim xlapp
    Dim xlbook1, xlsheet1
    Dim xlbook2, xlsheet2
    Set xlapp = createobject("Excel.Application")
    xlapp.visible = True
    Set xlbook1 = xlapp.workbooks.open("E:\Book1.xlsx",false,false)
    Set xlbook2 = xlapp.workbooks.open("E:\Book2.xlsx",false,false)
    Set xlsheet1 = xlapp.worksheets(1).UsedRange
    xlsheet1.copy
    xlbook1.close
    Set xlsheet2 = xlapp.worksheets(1)
    xlsheet2.paste
    xlbook2.save
    xlbook2.close

    Set xlbook2 = nothing
    Set xlapp = nothing
    </pre><hr />

    This script is opening the book1 but it is not pasting the content in book2

  2. #2
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Okie i found where the problem was...i have to use workbook object for worksheet....

    Now new problem...it is not saving book2 neither its appending the data in book2???

    what to do?

  3. #3
    Moderator
    Join Date
    Oct 2010
    Location
    Norway
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0
    Rajkumar

  4. #4
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Raj that script is giving error....that we cannot use UsedRange.Copy
    Iam attaching the screen shot of error.
    <font class="small">Code:</font><hr /><pre> Dim xlapp
    Dim xlbook1, xlsheet1
    Dim xlbook2, xlsheet2
    Set xlapp = createobject("Excel.Application")
    xlapp.visible = True
    Set xlbook1 = xlapp.workbooks.open("E:\Book1.xlsx",false,false)
    Set xlbook2 = xlapp.workbooks.open("E:\Book2.xlsx",false,false)
    Set xlsheet1 = xlbook1.worksheets(1).UsedRange.copy
    'xlsheet1.copy
    xlbook1.close
    Set xlsheet2 = xlbook2.worksheets(1).Range("A1").PasteSpecial
    Paste = xlvalues

    xlbook2.save
    xlbook2.close

    Set xlbook2 = nothing
    Set xlapp = nothing



    </pre><hr />

  5. #5
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Forgot to attach the screen shot..
    Attached Images Attached Images

  6. #6
    Moderator
    Join Date
    Oct 2010
    Location
    Norway
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Okay. This works for me on 2003. Hope the method is same for 2007, but will check it out.

    [ QUOTE ]

    Set objExcel = CreateObject("Excel.Application")
    objExcel.Visible = True
    Set objWorkbook1= objExcel.Workbooks.Open("C:\Forum\Book2.xls")
    Set objWorkbook2= objExcel.Workbooks.Open("C:\Forum\Book3.xls")
    objWorkbook1.Worksheets("Sheet1").UsedRange.Copy
    objWorkbook2.Worksheets("Sheet1").Range("A1").Past eSpecial Paste =xlValues
    objWorkbook1.save
    objWorkbook2.save
    objWorkbook1.close
    objWorkbook2.close
    set objExcel=nothing

    [/ QUOTE ]
    Rajkumar

  7. #7
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    okie..iam using 2007

  8. #8
    Moderator
    Join Date
    Oct 2010
    Location
    Norway
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Okay. The issue is with your script.

    Set xlsheet1 = xlbook1.worksheets(1).UsedRange.copy


    Try the code that I had quoted above. Just change the File names.
    Rajkumar

  9. #9
    Member
    Join Date
    Dec 2010
    Posts
    133
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Its working but its not appending the data in excel sheet...?

  10. #10
    Moderator
    Join Date
    Oct 2010
    Location
    Norway
    Posts
    3,265
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    1 Thread(s)
    Total Downloaded
    0

    Re: Excel Problem

    Here's an updated one. You have get the Used Row count and then set the start row for the next paste operation.

    objWorkbook2.Worksheets("Sheet1").Range("A1").Past eSpecial Paste =xlValues
    intRowCount = objWorkbook2.Worksheets("Sheet1").UsedRange.Rows.C ount

    RangeValue = "A"&amp;intRowCount+1
    objWorkbook2.Worksheets("Sheet1").Range(RangeValue ).PasteSpecial Paste =xlValues
    Rajkumar

 

 
Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.11%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 09:56 PM.

Copyright BetaSoft Inc.