Macro for getting data from another workbook

[mikecox39]

I'm treasurer of my HOA and every year I save my current Budget workbook, and create a new one using Save As and incrementing the integer by 1. For example, MB18 gets Saved As MB19.


I then clear the data using a macro to start the new year.


One of the problems with that is I don't get last years Income/Exp data for the new workbooks Budget sheet. I'm sure there is a formula or method that would pull that data in, but the name of the relevant workbook changes every year so the formula would have to change with it.


My goal is to create a macro that would run on June 30th of every year that creates a new, clean, budget workbook, with last year's Income/Exp data but to do that the previous years workbook name must be known.


I imagine a formula that would use the integer in the current workbook's filename to generated the previous years filename, by subtracting 1 from the integer of the current workbooks filename. So MB19 become MB18, where last years Income/Exp data resides.


The result could be saved in a cell and used in the formula for identifying last year's workbook, where the needed Income/Exp data is stored.


That would make it possible to automate the entire process.


Is that way too complicated? Is there a better option for accomplishing my goal?

Anyone interested in taking this on?


Here is my current workbook, cleared and ready for next year, but minus last years Income/Exp data needed for the Budget sheet.

https://app.box.com/s/tng7y5aybn345b2idvu80e8zhozatclb

 

[MoshiM]

I hope this helps

Option Explicit
Sub IMPORT_Old_Data()


End Sub


Dim Path As String, WB_New As Workbook, WB_Old As Workbook, Current_Year As Long, Previous_Year As Variant, _
Previous_WB_Name As String, File_Type As String


File_Type = "Extension type of workbook goes here ex: .xlsb "


Current_Year = Year(Date) 'gets the current year


Previous_Year = Current_Year - 1: Previous_Year = CStr(Previous_Year) 'determines the previous year and converts to a string
                                                                      
Previous_Year = Mid(Previous_Year, 3, 2)            'returns the last 2 digits of the previous year


Previous_WB_Name = "MB" & Previous_Year & File_Type


Path = "Assuming both files are in the same folder,then the path of the FOLDER goes here"


Path = Path & "\" & Previous_WB_Name


Call Open_Previous_Year_File(Previous_WB_Name, Path)


Set WB_Old = Workbooks(Previous_WB_Name)


'Now you can access the contents of the old workbook


'For example:


'Thisworkbook.worksheets("Name of Worksheet where you will place the data").range("A1").value2= WB_OLD.worksheets(Name of Worksheet that holds the data you want").range("A1").value2




WB_Old.Close False 'closes the old workbook


End Sub

Public Function Open_Previous_Year_File(Old_File_Name As String, Path As String) 'Open specific file or files


    Dim wb As Workbook
  
        Set wb = Workbooks.Open(Path) 'Opens File
    
        wb.Windows(1).Visible = False             'This line is optional but will ensure that the opened file will not be visible


End Function

 

[mikecox39]

I hope this helps

Option Explicit
Sub IMPORT_Old_Data()
End Sub
Dim Path As String, WB_New As Workbook, WB_Old As Workbook, Current_Year As Long, Previous_Year As Variant, _
Previous_WB_Name As String, File_Type As String
File_Type = "Extension type of workbook goes here ex: .xlsb "
Current_Year = Year(Date) 'gets the current year
Previous_Year = Current_Year - 1: Previous_Year = CStr(Previous_Year) 'determines the previous year and converts to a string
                                                                   
Previous_Year = Mid(Previous_Year, 3, 2)            'returns the last 2 digits of the previous year
Previous_WB_Name = "MB" & Previous_Year & File_Type
Path = "Assuming both files are in the same folder,then the path of the FOLDER goes here"
Path = Path & "\" & Previous_WB_Name
Call Open_Previous_Year_File(Previous_WB_Name, Path)
Set WB_Old = Workbooks(Previous_WB_Name)
'Now you can access the contents of the old workbook
'For example:
'Thisworkbook.worksheets("Name of Worksheet where you will place the data").range("A1").value2= WB_OLD.worksheets(Name of Worksheet that holds the data you want").range("A1").value2
WB_Old.Close False 'closes the old workbook
End Sub

Public Function Open_Previous_Year_File(Old_File_Name As String, Path As String) 'Open specific file or files
    Dim wb As Workbook
        Set wb = Workbooks.Open(Path) 'Opens File
   
        wb.Windows(1).Visible = False             'This line is optional but will ensure that the opened file will not be visible
End Function

Thanks, I really appreciate it.

I will add it to my workbook Module and see if I can get it to work. I will also study it very carefully to see if I can figure out how it works and what I can learn from it.