SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 2 of 2
  1. #1
    SQA Knight bklabel1's Avatar
    Join Date
    Sep 2012
    Location
    Kew Gardens, United States
    Posts
    2,596
    Post Thanks / Like
    Blog Entries
    1
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)
    Total Downloaded
    0

    Copy Sheets between workbooks

    Sometimes my .xls workbooks explode in size. I don't know what causes it. It goes from about 75K to 22MB without explanation.

    I don't want to copy one sheet at a time to a new workbook to fix it.
    I'm writing this function that opens two workbooks and copies sheets from the first to the second.
    I realize it may copy the files and still be enormous. I still want to know how to do this. I got stuck on the part where
    the copy occurs. Please tell me how to move an entire sheet. Or find the range and only copy that part into the new book.
    Thanks,

    Kevin

    Code:
    Function UtilityReduceWB(OldBook,NewBook)
    Dim objExcel,objWorkbook1, objWorkbook2
    Dim numSheetsWB1
     	' The input workbook sometimes becomes enormous in size such as 22 MB.  This script creates
    	' a smaller replacement workbook.
    
    	' A) Open the large Workbook into an Excel object.
    	 'B)  Create a new empty Workbook.
    	
    	'D)Copy each sheet from the old workbook to the new workbook.
    	'E)Save the new workbook.
    	'F)Close both workbooks.
    	'G)Close Excel
    	'H)Set objects to nothing.
    
    	' A) Open the large Workbook into an Excel object.
        Set objExcel = CreateObject("Excel.Application")
    	objExcel.Visible = True
    	Set objWorkbook1= objExcel.Workbooks.Open(OldBook)
    
    	'B)  Create a new empty Workbook.
    	Set objWorkbook2 = objExcel.Workbooks.Add
    
    	'C) Find out the number of sheets in the large workbook.
    	numSheetsWB1 = objWorkbook1.Worksheets.Count
    
    	'D)Copy each sheet from the old workbook to the new workbook.
    	For Counter = 1 To numSheetsWB1
    		objWorkbook1.Worksheet  <--I got stuck here.
    	Next
        	
    End Function

  2. #2
    SQA Knight bklabel1's Avatar
    Join Date
    Sep 2012
    Location
    Kew Gardens, United States
    Posts
    2,596
    Post Thanks / Like
    Blog Entries
    1
    Mentioned
    2 Post(s)
    Tagged
    2 Thread(s)
    Total Downloaded
    0
    I see this as an answer on some boards. QTP does not like the "colon equals"

    Code:
    	For each sh In objWorkbook1.Worksheets
    		sh.Copy After:=objWorkbook2.Sheets(objWorkbook2.Sheets.Count)
    	Next sh

 

 

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 13.64%
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 12:07 PM.

Copyright BetaSoft Inc.