Solved

File Search and Import Sheet with Date in file name

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Excel IF statement 4 21
Calculate number of months in Excel 13 26
pull a field from a programme into Excel 4 15
How to always round a decimal up 5 20
Introduction While answering a recent question (http:/Q_27311462.html), I created an alternative function to the Excel Concatenate() function that you might find useful.  I tested several solutions and share the results in this article as well as t…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

756 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