VBA Folder and File Selections with Wildcard.

[ianb]

HI All,

Does any one have a solution to this please.

I would like to search in a given folder e.g. T:\Audit\Data

Within this folder I will have various subfolders e.g.

T:\Audit\Data\2014Aug
T:\Audit\Data\2014Sep
T:\Audit\Data\2014Oct

I would like to select current year and month.

Then I would like to select the file within the folder if it was Report 11082014 as it is todays date

Some thing like this :

Attachments.Add T:\Audit\Data\2014Aug" & "Report " & Format(Now, "ddmmyyyy") & ".xlsx"

Does any one have a solution to this in excel.

Many thanks for your time and solution if you can provide a demo.

 

[Sathish KV]

Hi,

What is the final output you are expecting?

You need the file names in an excel?
You need to open that particular file?
You need to change a file name?

 

[ianb]

Hi,

It will be a wildcard in the coding :

1. I would like to search in a given folder e.g. T:\Audit\Data
2. I would like to select current year and month.

a)
T:\Audit\Data\2014Aug
T:\Audit\Data\2014Sep
T:\Audit\Data\2014Oct

The answer Excel VBA will give is that it will select T:\Audit\Data\2014Aug as the code will know it is looking in :\Audit\Data for the date in the format yyyymm

Once Excel VBA has set the folder it will then :

Select the file within the wildcard folder

Report 11082014 as it is todays date

Some thing like this :
Attachments.Add T:\Audit\Data\2014Aug" & "Report " & Format(Now, "ddmmyyyy") & ".xlsx"

All files will be named report ddmmyyyy in various folder it is that all August report files are found in the 2014Aug.

Final example:

today is : 25 september.

Program will :

1. goto folder : T:\Audit\Data
2. goto folder : T:\Audit\Data\2014Sep
3, goto file : :\Audit\Data\2014Sep\Report 25092014

And attach into an email else will display file is not present.

Many Thanks if any one can help.

 

[ianb]

I would like to attach the file in an email to send.

I have the coding for sendng an email to a user. it is the arrchment part on this critieria using wildcard for the folder and file name.

 

[shrivallabha]

Maybe like this:

Public Sub DoesFileExist()
Dim strFile As String
strFile = "T:\Audit\Data\" & Format(Now, "yyyymmm") & "\Report " & Format(Now, "ddmmyyyy")
If Len(Dir(strFile, vbNormal)) > 0 Then
  Attachments.Add strFile
Else
  MsgBox strFile & " doesn't exist!", vbInformation
End If
End Sub

 

[ianb]

Hi, Thank you for your response. I have placed this code into my program and it works in not finding the file yet when it does find the file it produces an error object code 424 on the line Attachments.Add strFile do you have any ideas why ?

Public Sub FileNfolder()
Dim strFile As String
strFile = "C:\Test\" & Format(Now, "yyyymmm") & "\Report " & Format(Now, "ddmmyy") & ".xlsx"
If Len(Dir(strFile, vbNormal)) > 0 Then
Attachments.Add strFile
Else
MsgBox strFile & " Not Found !", vbInformation
End If
End Sub

 

[ianb]

Hi, I have solved it. it was the location of my coding. I placed within the email code and not calling the code and it has worked. many thanks Shriva.....

Dim strFile As String

strFile = "C:\Test\" & Format(Now, "yyyymmm") & "\Banqueting Report " & Format(Now, "ddmmyy") & ".xlsx"

If Len(Dir(strFile, vbNormal)) > 0 Then

With OutMail
.To = strto
.CC = strcc
.BCC = strbcc
.Subject = strsub
.Body = strbody

.Attachments.Add strFile

.Send

End With

Set OutMail = Nothing
Set OutApp = Nothing

End If

End Sub

 

[shrivallabha]

Very good. I just copied and wrote the same line.

RTE 424: Object required. This will happen when the object which is not written (is implicit) before .(dot) but not qualified by using With block.

With SomeObject
    .Something.Work
End With

 

[ianb]

Thank you for your string, time and energy. Kind Regards Once again. Ian.