Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

File Search and Import Sheet with Date in file name

Posted on 2013-11-13
2
Medium Priority
?
260 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 2000 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

Independent Software Vendors: 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

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

972 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