Import worksheet based on filename and date

[Oryos]

Hi everyone,

I'm facing an issue that is I think easily solved but even with heavy googling I couldnt find a proper solution. So I hope some of you more advanced users can help me out here.
I'm using a macro in file A which is supposed to import a worksheet from file B. File B is being exported from another application on a day to day basis at around 3pm where the filename is ABC_yyyy_mm_dd.
So today the file is being stored in a folder with filename ABC_2016_02_18.

So my plan is to include in file A the following cell entries:
A1: Today's date
A2: path to file
A3: filename of file B with the date variable at the end

Whenever the macro in file A is being executed the macro should go into path written in Cell A2, find the file with filename from A3, compare the date in the filename with the date of Cell A1 and the worksheet if both dates are the same.

When I think about that it kinda sounds simple but I can't figure out how to do that.

Any ideas or thoughts on this are very much appreciated!

Thanks a lot!

[Bob Phillips]

If A1 has today, A2 the path, and A3 the filename, where does C42 come into it? Wouldn't the file de-facto have the same date as A1 if you are using A1 to determine what folder to look in?

[Oryos]

Hi xld,

thanks for your reply. I dont understand what you mean with C42? I never mentioned C42 in my previous post.
A1 would have the same date as the file name that is correct. However, that folder also stores all the other files from previous days and if someone would want to import the file from another day he would just change the date in A1 and the macro would search the file in that folder and import the worksheet based on the date.

does that make sense?

[SamT]

'Check if file exists
FileFound = Dir(Path & FileName&DateString)
If Len(FileFound) Then '0=false
'Open the book
Set BookB = Workbooks(Path & Filename & DateString).Open
End If

[shailendranr]

Hello Oryos

Instead of comparing file name with cell data

You can take latest updated file in folder as File B to import dat to File A , that is how am taking in my workplace as well,

[Oryos]

Hi SamT and shailendranr,

I tried your approach and defined the important cell names FileName, DateString and Path


Dim DateString As String, Filefound As String, Path As String, Filename As String

Filefound = Dir(Path & Filename & DateString)
If Len(Filefound) Then
Set BookB = Workbooks(Path & Filename & DateString).Open
End If

However it says out of Range. Is it because I didnt set the Path properly?

@shailendranr: What code do you use in order to import the latest updated file?

This is my current source code for importing files with manual select


Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
On Error GoTo Error
Filter = "Excel Files (*.xls),*.xls,"
FilterIndex = 3
Title = "Open file"
ChDrive ("D")
ChDir ("D:\Volume\Data")
With Application


Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open(.GetOpenFilename(Filter, FilterIndex, Title))

For Each WS In SourceWB.Worksheets
WS.Copy After:=WB.Sheets(WB.Sheets.Count)
Next WS

SourceWB.Close savechanges:=False
Set WS = Nothing
Set SourceWB = Nothing

WB.Activate
ASheet.Select
Set ASheet = Nothing
Set WB = Nothing

Application.EnableEvents = True
End With

[snb]

A oneliner suffices:


Sub M_snb()
if dir("D:\Volume\Data\ABC_" & format(date,"yyy_mm_dd"))<>"" then workbooks.open "D:\Volume\Data\ABC_" & format(date,"yyy_mm_dd")
End Sub

[SamT]

Is there a Path Delimiter ("\") between Path and Filename?

[Oryos]

A oneliner suffices:


Sub M_snb()
if dir("D:\Volume\Data\ABC_" & format(date,"yyy_mm_dd"))<>"" then workbooks.open "D:\Volume\Data\ABC_" & format(date,"yyy_mm_dd")
End Sub

Am I supposed to define a cell in my worksheet "format" with the file format I'm searching and "date" with the date I want to import?
I did that and nothing gets important.
Maybe I dont fully understand how I'm supposed to use this code or its not clear what I want to achieve.

This is what I want to achieve:
I'm using an excel sheet (lets call it file A) where I have to import a worksheet from another file (file B). I have to do that every day once. The file I'm going to import is located with other excel files in a specific folder and the file format of file B is like "ABC_yyyy_mm_dd"

So in a configuration sheet in file A i put in each of a cell:
1. path to file B
2. file B format
3. current day with =Today.

I thought I could use a vba code that applies the values in these cells to determine where the file is, to find file B and also to import the worksheet from file B according to the date (file format must have the same date as cell nr. 3).

@SamT:
is your post related to my previous post or to the one of snb?

Thank you all for your patience and contribution!

[SamT]

It is obvious to us from the sophisticated code your post #6 that you know quite a bit about programming with VBA.


@SamT:
is your post related to my previous post or to the one of snb?You should assume that it is addressed to you and check your code, even if you think I did address it to snb.

Place the Cursor inside the word "Format" in the VBA Editor and press F1.

[snb]

An Excel file can't have a name without extension.
You can hardcode the path, no need to read it from the file if it's 'constant'.
'Date' is part of VBA, so it's useless to read it from the file.
You should be more specific.


Sub M_snb()
If dir("D:\Volume\Data\ABC_" & format(date,"yyyy_mm_dd")&".xlsx")<>"" Then
with getobject("D:\Volume\Data\ABC_" & format(date,"yyyy_mm_dd") & ".xlsx")
. copy what ??? (you didn't specify....)
end with
end if
End Sub


PS. Kennst Du Office-loesung.de/p/ ?

[SamT]

IF:
Cell "A1" = C:\MyFolder1\MyFolder2\ <--Note trailing "\"
Cell "B1" = ABC <--Note No Extension
Cell "C1" = 2016_02_25

With getobject(Range("A1") & Range("B1") & "_" & Range("C1").Text & ".xlsx")

IF Cell "A1" = C:\MyFolder1\MyFolder2 Then <--Note no trailing "\"

With getobject(Range("A1") & "\" & Range("B1") & "_" & Range("C1").Text & ".xlsx")

IF Cell "C1" = 02-25-2016 or other Date Format then

With getobject(Range("A1") & "\" & Range("B1") & "_" & Format(Range("C1"), "mm_dd_yyyy") & ".xlsx")

IF the Filename in the Cell has the Extension, then change "& Range("B1) &" in the above lines of code to
"& Replace(Range("B1"), ".xlsx", "") &"

[Oryos]

thanks you two for your response.

I got it working now with this:


Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
Filter = "Excel Files (*.xls),*.xls,"
FilterIndex = 3
ChDrive ("D")
ChDir ("D:\Volume\Data")
With Application


Dim WB As Workbook
Dim SourceWB As Workbook
Dim WS As Worksheet
Dim ASheet As Worksheet

Application.ScreenUpdating = False
Application.EnableEvents = False

Set WB = ActiveWorkbook
Set ASheet = ActiveSheet
Set SourceWB = Workbooks.Open("doc_" & Format(Date, "yyyy_mm_dd"))

For Each WS In SourceWB.Worksheets
WS.Copy After:=WB.Sheets(WB.Sheets.Count)
Next WS

SourceWB.Close savechanges:=False
Set WS = Nothing
Set SourceWB = Nothing

WB.Activate
ASheet.Select
Set ASheet = Nothing
Set WB = Nothing

Application.EnableEvents = True
End With

End Sub