Copy Excel sheet (values only) [Resolved]

[nightshift]

Hi - I've read through just about every forum I can find and I hope someone can help me with a problem slightly different to anything I've read.

I want to write a vb6 app. to copy a master pricing Excel Workbook (Master.xls) over to a blank Excel workbook (Pricing.xls) with just the values from each sheet in Master.xls (Don't want clients to see formulaes used in Master pricing workbook).

Number of worksheets and rows of data for each worksheet in Master.xls is variable.

I need to use non version specific coding (eg. Dim xlMaster, xlPricing as Object) so I don't have problems on client end.

Would appreciate any help to point me in the right direction.

thanks.

[d3gerald]

maybe this link can help you

http://vbforums.com/showthread.php?t=391154

[jcis]

Just made this, it copies all data in a workbook and add it to a new workbook pasting all except formulas, works without a reference to Excel and works fine in Excel XP, but I'm not sure about older versions, maybe someone who has an older version could test this code.

Option Explicit
 
Private Sub Command1_Click()
    CloneWorkbook "C:\123.xls"
End Sub
 
Private Sub CloneWorkbook (pWBPath As String)
'Consts so you don't need a reference to Excel library
Const lcnstPasteValuesAndFormats    As Integer = 12
Const lcnstNone                     As Long = -4142
'Declares
Dim objExcel            As Object
Dim objSourceWorkBook   As Object
Dim objDestWorkBook     As Object
Dim i                   As Integer
 
    Set objExcel = CreateObject("Excel.Application")
    Set objSourceWorkBook = objExcel.Workbooks.Open(pWBPath)
    Set objDestWorkBook = objExcel.Workbooks.Add
    
    Clipboard.Clear 'Clear Clipboard
    
    'If target has less sheets than source add sheets
    Do While objSourceWorkBook.Sheets.Count < objDestWorkBook.Sheets.Count
        objDestWorkBook.Sheets.Add
    Loop
    
    For i = 1 To objSourceWorkBook.Sheets.Count 'Loop for each sheet
        objSourceWorkBook.Sheets(i).Cells.Copy  'Copy source sheet
        objDestWorkBook.Sheets(i).Select        'Select target sheet
        'Paste all except formulas
        objExcel.Selection.PasteSpecial lcnstPasteValuesAndFormats, lcnstNone, False, False
        objDestWorkBook.Sheets(i).Range("A1").Select 'Leave just first cell selected
    Next
    objDestWorkBook.Sheets(1).Select ' Select first sheet
    
    Clipboard.Clear                 'Remove last sheet from clipboard
    objExcel.DisplayAlerts = False  'Dont ask about saving
    objSourceWorkBook.Close         'Close original Workbook
    Set objSourceWorkBook = Nothing
    
    'Show Excel with the new created Workbook, you could save it and close it here also
    objExcel.Visible = True
End Sub

[TysonLPrice]

I tried it ASIS on Excel 97 and got an error at:

objExcel.Selection.PasteSpecial lcnstPasteValuesAndFormats, lcnstNone, False, False

The error was "PasteSpecial Method of Range Class Failed"

Run-Time error "1004"

[yrwyddfa]

It's more efficient to open the XL workbook/Sheet using the ADO provider. I can't find code examples at the minute, anyone?

[nightshift]

Many thanks jcis - not sure about that other users error, but worked fine for me. Saving me going mad. Cheers