Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Output to File (2000)

    I need to replicate the Do.Cmd Transferspreadsheet function from access in Excel.

    ie: I want to add some code on a button in excel to add a particular sheet to a new workbook on the c: drive.
    I need to add about 6 pages sequentially to this workbook.

    Anyone know the command to do this in VBA? --- I've searched for hours in VBA help, but cannot see which command would do it.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Output to File (2000)

    In Excel VBA, you would create a new workbook, then copy the worksheet to it, save the new workbook and close it. Sample code:

    Dim wbkSource As Workbook
    Dim wbkTarget As Workbook
    Dim wshSource As Worksheet

    Set wbkSource = Workbooks("MyWorkbook.xls")
    Set wshSource = wbkSource.Worksheets("MySheet")

    Set wbkTarget = Workbooks.Add

    wshSource.Copy Before:=wbkTarget.Sheets(1)
    wbkTarget.SaveAs Filename:= "C:ExcelTest.xls"
    wbkTarget.Close SaveChanges:=False

    Of course, you should replace the names used here, they can be literal strings or string variables.

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to File (2000)

    Hi Hans,

    Copying one sheet out of a workbook can cause links to the source workbook to appear when this sheet contains links to another sheet.
    To avoid that, copy all sheets in one go:

    Dim oNewbook as Workbook
    Workbooks("MyBook").Sheets.Copy
    Set oNewbook=Activeworkbook
    oNewbook.Save "c:dataTest.xls"
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    3 Star Lounger
    Join Date
    Sep 2002
    Posts
    294
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to File (2000)

    Hans,
    I tried this approach, it saves out the sheet, but then, unlike the access command, i cannot add another sheet to it afterwards.

    Essentially, i have one page in a workbook, which i want to save out, change some things on this page, then save it out again, adding to the file.


    As Jan says, the links are retained as well, and it will prompt you to update the links on opening. (which is not that bad, if i can save every sheet that i need to)

    Many thanks

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Output to File (2000)

    You don't append using the Hans' code since the target workbook is ALWAYS a new one because of the code:

    <pre> Set wbkTarget = Workbooks.Add </pre>


    You could something like this procedure with an optional parameter for the target. If target is not entered it creates a new one, otherwise it appends:

    <pre>Option Explicit
    Sub NewAppendSheets(sWbkSource As String, _
    sShtSource As String, _
    Optional sTarget As String = "")

    Dim wbkSource As Workbook
    Dim wshSource As Worksheet
    Dim wbkTarget As Workbook

    Set wbkSource = Workbooks(sWbkSource)
    Set wshSource = wbkSource.Worksheets(sShtSource)

    If sTarget = "" Then
    Set wbkTarget = Workbooks.Add
    Else
    Set wbkTarget = Workbooks(sTarget)
    End If

    wshSource.Copy Before:=wbkTarget.Sheets(1)
    End Sub
    </pre>

    You could call it from within another routine something like:

    <pre>Sub test()
    'some code here
    Call NewAppendSheets("MyWorkbook.xls", "MySheet") 'this creates new wbk
    activeworkbook.SaveAs FileName:="C:Test.xls" 'this saves and names new wbk

    'more code here perhaps

    Call NewAppendSheets("MyWorkbook.xls", "MySheet2", "test.xls") 'this appends

    'even more code here

    End Sub
    </pre>

    Steve

  6. #6
    Silver Lounger
    Join Date
    Dec 2000
    Location
    California, USA
    Posts
    1,758
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Output to File (2000)

    Hans,

    How does one handle the PasteSpecial feature?

    Such as: Note! The code is an example and does not work
    wshSource.Copy Before:=wbkTarget.Sheets(1), PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False


    Regards,
    John

  7. #7
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Output to File (2000)

    How about this?

    <pre> Set wshSource = wbkTarget.Worksheets("Sheet1")
    wshSource.Copy Before:=wbkTarget.Sheets(1)
    With Range("A1")
    .Cells.Copy
    .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
    SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False</pre>


    Steve

Posting Permissions

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