Solved

File Search and Import Sheet with Date in file name

Posted on 2013-11-13
2
258 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

617 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