Macro for getting data from another workbook

mikecox39

Active Member
Joined
Mar 5, 2014
Messages
251
Office Version
  1. 365
Platform
  1. Windows
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? :confused:


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
 

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
I hope this helps
Code:
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
Code:
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
 
Upvote 0
I hope this helps
Code:
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
Code:
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.
 
Upvote 0

Forum statistics

Threads
1,213,559
Messages
6,114,302
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top