Solved

File Search and Import Sheet with Date in file name

Posted on 2013-11-13
2
254 Views
Last Modified: 2013-11-15
I have attached a file and need help.  I found code that is in the attached but it does not do exactly what I need.

What I need help with is.  

1.  Be able to find a file with date in file name wherer the date is less than 12 months from date on Sheet1 B2 then import Sheet2  into current workbook to Sheet2.

Example;  If B2 = 9/31/13 then need to import file c:\filename_09_31_12.xls

Unless the file has revised at the end  as c:\filename_09_31-12_revised.xls then I need to import sheet2 of that file instead.

Thanks in advance
FileSearch.zip
0
Comment
Question by:leezac
2 Comments
 
LVL 81

Accepted Solution

by:
byundt earned 500 total points
ID: 39647005
I modified the macro to look for a file with date exactly 12 months prior to the value in cell B1. If found, the macro will import Sheet2 from that workbook into the active workbook. If workbook has "Revised" in its filename, Sheet2 of that workbook is still imported into the active workbook.
Sub CopySheets()
    
Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet
Dim flPath As String, flName As String, sDate As String

Application.ScreenUpdating = False
Application.EnableEvents = False

Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
sDate = Format(DateAdd("yyyy", -1, ASheet.Range("B1").Value), "_mm_dd_yy")
flPath = WB.Path & Application.PathSeparator
flName = Dir(flPath & "*" & sDate & "*.xls")
If flName <> "" Then
    Set SourceWB = Workbooks.Open(WB.Path & flName)  'Modify for finding date 12 months from date in B2 of Sheet1
    
    'Copies Sheet2 of the SourceWB to the end of original wb:
    SourceWB.Worksheets("Sheet2").Copy after:=WB.Sheets(WB.Sheets.Count)
        
    SourceWB.Close SaveChanges:=False
End If

Application.EnableEvents = True
    
End Sub

Open in new window

0
 

Author Comment

by:leezac
ID: 39648022
Thanks - I am going to look at today, but am going to have to post another question regarding the folders  and finding the file.  I realized there is more to it....
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question