Hi,I have a requirement that i am trying to code,i have 2 environments beta and prod that has reports (30+).these reports are in .xls format.Eg:Beta report:AUD210ba_-_Research_Ratings_Audit.xls
and prod report is AUD210ba_-_Research_Ratings_Audit_Prod.xls.similarly with all the reports.the requirement is i have to browse thru the directory of the given path and get the first report and browse thru the same directory and match this name with "_Prod" extension file and then compare 2 files sheet by sheet and create a report.I have the compare subroutine and the report creation.but the challenge is i am unable to loop thru the files and get the files with simliar names.Need advice.

There's 2 approaches I can think of off the top of my head:

1. Loop the folder for files without "_Prod" in the name;
In this approach use Replace(f, ".xls", "_Prod.xls") to get its
associated file.

2. Loop the folder for files with "_Prod" in the name;
In this approach use Replace(f, "_Prod", "") to get its
associated file.

In both approaches test the filename using InStr()...

Approach1:
If Not InStr(f, "_Prod") 0 Then...
'sAssociatedFile = Replace(f, ".xls", "_Prod.xls")

Approach2:
If InStr(f, "_Prod") 0 Then...
'sAssociatedFile = Replace(f, "_Prod", "")

Note that this assumes using VB's Dir() function on the folder path,
and assigning the next filename to a Variant variable (f)...

Dim f As String
f = Dir(sFileFolder & "*.xls", vbDirectory)
Do While Len(f)
'//do stuff with current file
f = Dir() '//get next file
Loop

...where sFileFolder is the full path including a backslash.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

H Garry,

Thanks for the reply.i tried implementing your approach,and it worked fine.but the only problem i have is ,if i modilfied the file today and i am running the same set of files tommorow,its taking the files based on the date modified.so this is like, i have to compare report101.xls with report101_Prod.xls.but if i modify report101_Prod.xls and run the macro,the first file it takes is the report101_Prod.xls.How to rectify this issue,i want to just loop thru the files as it is,i dont want the files to be picked up by the date modified

Thanks,
-L

H Garry,

Thanks for the reply.i tried implementing your approach,and it worked
fine.but the only problem i have is ,if i modilfied the file today
and i am running the same set of files tommorow,its taking the files
based on the date modified.so this is like, i have to compare
report101.xls with report101_Prod.xls.but if i modify
report101_Prod.xls and run the macro,the first file it takes is the
report101_Prod.xls.How to rectify this issue,i want to just loop thru
the files as it is,i dont want the files to be picked up by the date
modified

Thanks,
-L

Using my suggested approach[es], the files are picked up according to
the presence (or absence) of "_Prod" in the filename. This has nothing
to do with date modified. Perhaps it will help if you redefine your
criteria more clearly, and show the code you're using for doing this...

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Garry,

Please find the below code:

Sub getfile()
Dim f As String
Dim f1, f2, FN2, path As String
Dim wbk2 As Workbook, NewBook As Workbook
Dim xRow As Integer
path = "P:\test2\"
path1 = "P:\test2\Report\"
'f = Dir(path & "*.xls", vbDirectory)
'f3 = Replace(f, ".xls", "_Prod.xls")
Set NewBook = Workbooks.Add
With NewBook
.Title = "Report"
.Subject = "comparision"
.SaveAs Filename:=path1 & "Report-" & Format(Now, "ddmmyyyyhhmm") & ".xlsx"
End With
FN2 = NewBook.Name
Set wbk2 = Application.Workbooks.Open(path1 & FN2)

wbk2.Sheets(1).Range("A1").Value = "Report"
wbk2.Sheets(1).Range("B1").Value = "Sub Report"
wbk2.Sheets(1).Range("C1").Value = "Status"
wbk2.Sheets(1).Range("D1").Value = "Result"
wbk2.Sheets(1).Range("A1:D1").Interior.ColorIndex = 15
wbk2.Sheets(1).Range("A1:D1").Font.Bold = True
wbk2.Close SaveChanges:=True
Set objFso = CreateObject("Scripting.FileSystemObject")
Set objFld = objFso.GetFolder(path)
For Each objFile In objFld.Files
f1 = objFile.Name
f3 = Replace(f1, ".xls", "")
If Not InStr(f1, "_Prod") 0 Then
f2 = Replace(f1, ".xls", "_Prod.xls")
Set mySource = CreateObject("Scripting.FileSystemObject")
Set myFile = objFso.GetFolder(path)
For Each File In myFile.Files
If File < " " Then
If File.Name = f2 Then
Set wbk2 = Application.Workbooks.Open(path1 & FN2)
compare f1, f2, path, wbk2, FN2
Exit For
End If

Else
End If
Next File

Set wbk2 = Application.Workbooks.Open(path1 & FN2)
Set ws = wbk2.Sheets(1)
xRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row + 1
ws.Cells(xRow, 1).Value = f3
ws.Cells(xRow, 2).Value = " "
ws.Cells(xRow, 3).Value = "Fail"
ws.Cells(xRow, 4).Value = "No Prod File Found"
wbk2.Close SaveChanges:=True
End If
Next objFile
End Sub


This code checks for the files and returns if the prod file is not found and writes to a report.but there seems to be a problem with the looping.

Please see the below report that is generated:

Ok.., give me some time to digest this as I find it confusing at 1st
glance. I'll probably rewrite it without using FSO, though, because
using Dir() works just fine without the added dependancy of the
Scripting lib, and makes coding easier to understand/manage (IMO).

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion