Solved

File Search and Import Sheet with Date in file name

Posted on 2013-11-13
2
253 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

932 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now