PDA

View Full Version : Solved: Open a Excel File from Specific Path and with a Specific Name and Date



bvsramesh
02-27-2012, 11:26 PM
Hi Team,

Please help me on the below code.

I am useing the below code to open a excel file in a specific path and with a common starting Name and ending with previous date.

eg: I want to open a file in below path : "C:\Cash\CMD\"

In the above path lot of files starting common name with "Cash_CMD" but we can differentiate files with Date (eg: "Cash_CMD_28.02.2012.xls")

The original File with path like the : C:\Cash\CMD\Cash_CMD_28.02.2012.xls

Plz find the code and correct me what changes can do to get result like what i need.


Code :

Sub OpenFile()
Dim Filter As String, Title As String
Dim FilterIndex As Integer
Dim Filename As Variant
Filter = "Excel Files (*.xls),*.xls,"
Title = "Select a File to Open"
' Select Start Drive & Path
ChDrive ("C")
ChDir ("C:\Cash\CMD")
With Application

'Filename = .GetOpenFilename(Filter, FilterIndex, Title)
Filename = .GetOpenFilename("Cash_CMD " & Format(Now()-1, "dd.mm.yy") & ".xls"
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
If Filename = False Then
MsgBox "No file was selected."
Exit Sub
End If
Workbooks.Open Filename
MsgBox Filename, vbInformation, "File Opened" ' This can be removed
End Sub

mdmackillop
02-28-2012, 07:10 AM
Check the FileName, you're missing an underscore, and a couple of Ys.

frank_m
02-28-2012, 08:44 AM
Hi bvsramesh,

I don't think that .GetOpenFilename can be used like that.
I believe it only opens a File Browser showing all the files in a folder that match the file type/s that you want, not by the specific file name.
- I assume you know that your file name uses yesterdays date.

The code I provided below is the method that I like to use.

If you have a good reason for useing 2 y's instead of 4 in your file names, this seem to work either way. (I also gave an example of using a partial name with wild card *)

Edit: Added the missing underscore that mdmackillop pointed out

Sub Open_File()

Dim Filename As String, MyFileDialog As FileDialog
ChDrive "C:\"
ChDir "C:\Cash\CMD\" 'path to folder
Filename = "Cash_CMD_" & Format(Now() - 1, "dd.mm.yyyy") & ".xls" ' or "dd.mm.yy")
'Filename = "Cash_CMD*" '<<--list all files containing partial name
Set MyFileDialog = Application.FileDialog(msoFileDialogOpen)

With MyFileDialog
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.InitialView = msoFileDialogViewDetails
.Title = "Default File Name is: " & Filename '"Select a File to Open"
.InitialFileName = Filename
If .Show = True Then
Workbooks.Open .SelectedItems(1) 'or below to open other file types
'ActiveWorkbook.FollowHyperlink Address:=.SelectedItems(1)
End If
End With
With Application
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With

End Sub

bvsramesh
02-29-2012, 01:01 AM
Hi Frank / Team,

Thank you very much... This is working now... actually the above code is to Vlookup with present day file with the Previous day file. Is there any other way that i can do this with or with opening Previous file.

And one more important thing i have missed on the above code is whether the previous day is "friday" the code will not pick the file when we try on "monday" as the moday's previous day is "sunday". please suggest on this also.

once again thank you very much for the help........

Regards,

BVSR

frank_m
02-29-2012, 03:54 AM
Edit#3 - I just noticed that if you clear the file name in the File Browser and push F5 to referesh, you can then browse for other files.

Probably not the simplest way:

Sub Open_File()

Dim Filename As String, MyFileDialog As FileDialog
ChDrive "C:\"
ChDir "C:\Cash\CMD\" 'path to folder
'Filename = "Cash_CMD_*" '<<--list all files containing partial name

Select Case Weekday(Now) 'Sunday is day(1)
Case 7 'if Saturday, look for Thursdays File
Filename = "Cash_CMD_" _
& Format(Now() - 2, "dd.mm.yyyy") & ".xls"

Case 1, 2 'if Sunday, look for Thursday, if Monday look for Friday
Filename = "Cash_CMD_" _
& Format(Now() - 3, "dd.mm.yyyy") & ".xls"

Case 3, 4, 5, 6 'if Tuesday thru Friday(day 3 thru 6), look for yesterdays File
Filename = "Cash_CMD_" _
& Format(Now() - 1, "dd.mm.yyyy") & ".xls"
End Select

Set MyFileDialog = Application.FileDialog(msoFileDialogOpen)

With MyFileDialog
.AllowMultiSelect = False
.Filters.Clear
.Filters.Add "Excel Files", "*.xls"
.InitialView = msoFileDialogViewDetails
.Title = "Default File Name is: " & Filename '"Select a File to Open"
.InitialFileName = Filename
If .Show = True Then
Workbooks.Open .SelectedItems(1) 'or below to open other file types
'ActiveWorkbook.FollowHyperlink Address:=.SelectedItems(1)
End If
End With
With Application
ChDrive (Left(.DefaultFilePath, 1))
ChDir (.DefaultFilePath)
End With
End Sub

Edit#1 or open the file directly without the File Browser, since when coded like this, it's not letting browse for other files anyway<--See Edit#3 at the top of this post

Edit# 2 corrected path

Sub OpenFile_WithOutFileBrowser()

Dim Filename As String

Select Case Weekday(Now) 'Sunday is day(1)

Case 7 'if Saturday, look for Thursdays File
Filename = "C:\Cash\CMD\Cash_CMD_" _
& Format(Now() - 2, "dd.mm.yyyy") & ".xls"

Case 1, 2 'if Sunday, look for Thursday, if Monday look for Friday
Filename = "C:\Cash\CMD\Cash_CMD_" _
& Format(Now() - 3, "dd.mm.yyyy") & ".xls"

Case 3, 4, 5, 6 'if day(3 thru 6), look for yesterdays File
Filename = "C:\Cash\CMD\Cash_CMD_" _
& Format(Now() - 1, "dd.mm.yyyy") & ".xls"

End Select

On Error GoTo errorHandler

Workbooks.Open Filename

Exit Sub
errorHandler:
MsgBox "File not found"
End Sub

mdmackillop
02-29-2012, 05:58 AM
To avoid repetion, so easier to debug


Dim Filename As String
Dim a As Long
Select Case Weekday(Now) 'Sunday is day(1)
Case 1, 2 'if Sunday, look for Thursday, if Monday look for Friday
a = 3
Case 7 'if Saturday, look for Thursdays File
a = 2
Case Else
a = 1
End Select
Filename = "C:\Cash\CMD\Cash_CMD_" _
& Format(Now() - a, "dd.mm.yyyy") & ".xls"

frank_m
02-29-2012, 06:22 AM
To avoid repetion, so easier to debug
much better indeed :thumb - Consolidating it that way would have dawned on me eventualy :eek:

bvsramesh
02-29-2012, 06:28 AM
Hi Frank,

Super ... thank you very much... i cann't say more than this but this is too good for me to solve my problem...

regards

BVSR

bvsramesh
03-02-2012, 06:39 AM
Hi Frank the below code is which is using for vlook up with new file and old file. is there any other way other than open the old file and do it like below code. pls help on this too.

As the below code is running good but still i want to smooth this process.

regards

BVSR

Sub Pre_Day_CMD_FIle_and_Vlookup_1()

Application.ScreenUpdating = False
Dim Filename As String

Select Case Weekday(Now) 'Sunday is day(1)

Case 7 'if Saturday, look for Thursdays File
Filename = "C:\Cash\CMD\Cash_CMD_” _
& Format(Now() - 2, "dd.mm.yyyy") & ".xls"

Case 1, 2 'if Sunday, look for Thursday, if Monday look for Friday
Filename = "C:\Cash\CMD\Cash_CMD_” _
& Format(Now() - 3, "dd.mm.yyyy") & ".xls"

Case 3, 4, 5, 6 'if day(3 thru 6), look for yesterdays File
Filename = "C:\Cash\CMD\Cash_CMD_” _
& Format(Now() - 3, "dd.mm.yyyy") & ".xls"

End Select

On Error GoTo errorHandler

Workbooks.Open Filename

Worksheets("CMD_Cash").Select

‘ This the sheet where the vlookup in place with the today’s sheet

Sheets("CMD_Cash").Copy Before:=Workbooks( _
"Cash_CMD_dd.mm.yyyy.xls".Sheets(7) ‘(Today’s File)

‘The above sheet is Yesterday’s Sheet that is moving to present Day’s Sheet using move or copy (here "Cash_CMD_dd.mm.yyyy.xls" is today’s File)

Selection.Columns.AutoFit

Exit Sub
errorHandler:
MsgBox "File not found"
End Sub





Sub Pre_Day_CMD_FIle_and_Vlookup_2()

‘ The below action all in today’s file after we move the old sheet to now file.

Application.ScreenUpdating = False

Windows("Cash_CMD_dd.mm.yyyy.xls").Activate

‘The above file is Today’s File

Sheets("CMD_Cash").Select
Sheets("CMD_Cash (2)").Select
Range("L4").Select

Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Sheets("CMD_Cash").Select
Range("L4").Select
ActiveSheet.Paste

Range("L5").Select
Application.CutCopyMode = False

‘vlookup with current day sheet accounts number with previous day’s account and with other columns from Col “L” to Col “AD” generally count will be 300 but I have here given 1000 in case if the count is increases

ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,10,0)"
Range("L5").Select

Selection.Copy
Range("M5:AD5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("M5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,11,0)"
Range("N5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,12,0)"
Range("O5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,13,0)"
Range("P5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,14,0)"
Range("Q5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,15,0)"
Range("R5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,16,0)"
Range("S5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,17,0)"
Range("T5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,18,0)"
Range("U5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,19,0)"
Range("V5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,20,0)"
Range("W5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,21,0)"
Range("X5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,22,0)"
Range("Y5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,23,0)"
Range("Z5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,24,0)"
Range("AA5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,25,0)"
Range("AB5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,26,0)"
Range("AC5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,27,0)"
Range("AD5").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(R5C3:R1000C3,'CMD_Cash (2)'!R5C3:R1000C30,28,0)"
Range("L5").Select

End Sub

bvsramesh
03-04-2012, 06:34 AM
Hi Team,

Plz provide a solution for the above code as when i move the sheet from previous day's file it is giving error like only first 255 letters only will carry from any of the column and this will a result some of the comments of any column which will more that 255 letters will carry to the present day's file when i vlookup . instead of that if i have the code like vlookup with out opening the file that will give a good result. plz help me.

mdmackillop
03-04-2012, 07:08 AM
How do you intend that we test your code?