CODEWITHSUNDEEP
excelvba
user3791478
user3791478

Reputation: 13

How can I Save an Excel Workbook in a different location?

I am writing a program that is copying and pasting data from one workbook to another. I want to save one workbook in a different location, close it, then open a new workbook from a file and do the same thing (I am copying data from multiple workbooks and pasting this data into one master workbook). I also need help reversing the signs (for example: I will copy the number 1 from wbk1 and I need to paste -1 in wbk2).

Function GetBook() As String
    GetBook = ActiveWorkbook.Name
End Function

Sub Paste()
    Dim wbk As Workbook
    Dim wbkH As Workbook
    Dim fso As Object
    Dim COID As String


    Set wbk = Workbooks("0_Master Footnote Operating Lease May 2014_LIVE_essbase")
    COID = "6985" 'Facility number used to search in wbk

    Set wbkH = Workbooks(GetBook)

    'Subtractions
    wbkH.Activate 'Select Hospitals document
    Sheets("Additions & Expirations").Select 'select ws
    
    Columns("G:G").Select
    Range("G:G").Activate
    Selection.Find(What:="Total Lease", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select 'Selects entire row
    
    With ActiveCell
        Range(Cells(.Row, "H"), Cells(.Row, "H")).Select 'Select first total in column
        Selection.Copy
        
        'enter in hospitals COID
        wbk.Activate
        Sheets("Compare CY to PY").Select
    
        Columns("C:C").Select
        Range("C:C").Activate
        Selection.Find(What:=COID, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
        ActiveCell.EntireRow.Select
        With ActiveCell
        Range(Cells(.Row, "J"), Cells(.Row, "J")).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False 'clears clipboard
        End With  'I NEED TO CHANGE SIGN ON THIS POSTED VALUE (EX. 1 TO -1)
    End With

    wbkH.Activate 'Select Hospitals document
    Sheets("Misc Reconciling Items").Select 'select ws
    
    Columns("A:A").Select
    Range("A:A").Activate
    Selection.Find(What:="Annualized", After:=ActiveCell, LookIn:=xlValues, _
        LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
        MatchCase:=False, SearchFormat:=False).Activate
    ActiveCell.EntireRow.Select 'Selects entire row
    
    With ActiveCell
        Range(Cells(.Row, "D"), Cells(.Row, "D")).Select 'Select first total in column
        Selection.Copy
        
        'enter in hospitals COID
        wbk.Activate
        Sheets("Compare CY to PY").Select
    
        Columns("C:C").Select
        Range("C:C").Activate
        Selection.Find(What:=COID, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Activate
        
        ActiveCell.EntireRow.Select
        With ActiveCell
        Range(Cells(.Row, "L"), Cells(.Row, "L")).PasteSpecial Paste:=xlPasteValues
        Application.CutCopyMode = False 'clears clipboard
        End With 'I NEED TO CHANGE SIGN ON THIS POSTED VALUE (EX. 1 TO -1)
    End With

    wbkH.Activate
    ActiveWorkbook.SaveAs ("C:\Program Files\" & GetBook) 'THIS CODE WONT WORK AND I HAVE TRIED VARIOUS CODES.

Upvotes: 1

Views: 719

Answers (1)

Alexander Bell
Alexander Bell

Reputation: 7918

As per you topic header, it seems that the only problem is just saving Excel Workbook (re: the line ActiveWorkbook.SaveAs ("C:\Program Files\" & GetBook) 'THIS CODE WONT WORK AND I HAVE TRIED VARIOUS CODES). If this is correct, then couple code snippets can help (C#):

Exampl 1. Close and save

object misValue = System.Reflection.Missing.Value;
ActiveWorkbook.Close(true, filePath, misValue);

Example 2 (from http://msdn.microsoft.com/en-us/library/h1e33e36.aspx)

this.SaveAs(@"C:\Book1.xml", missing,
    missing, missing, missing, missing, Excel.XlSaveAsAccessMode.xlNoChange,
    missing, missing, missing, missing, missing);

Example 3 (from http://msdn.microsoft.com/en-us/library/h1e33e36.aspx)

this.Application.ActiveWorkbook.SaveAs(@"C:\Test\Book1.xml", 
    Excel.XlSaveAsAccessMode.xlNoChange);

Regards,

Upvotes: 2

Related Questions