Need Urgent help if possible please help. Lookup the workbook name.

[pedie]

I have code

Sub try121()
Dim wb As Workbook
Windows("[COLOR=blue]Backup Data September 28, 2010.xlsm[/COLOR]").Activate
Sheets("Dater").Select
LstRw = Cells(Rows.Count, "O").End(xlUp).Row
  Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"
  Range("B2:B" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
   ThisWorkbook.Sheets("Data").Cells(Rows.Count, "G").End(xlUp)(2)
  AutoFilterMode = False
ThisWorkbook.Activate
End Sub

The thing is that my file name changes every day ("Backup Data September 28, 2010.xlsm") just the date.
Can some hel me set up how to look for the workbook and select
like Backup Data & month & i &,.xlsm etc???

Not sure how to set this question up...

i want to be able to look for file name through loop or something....
yesterday someone gave me this code which might help but not sure how to set this up.

Sub try2()
Dim ws As Worksheet
For i = 1 To 30  'to  6? to whatever.
  On Error Resume Next
  Set ws = Workbooks("Backup Data September (" & i & "), 2010.xls").ActiveSheet
  On Error GoTo 0
  If Not ws Is Nothing Then
    Workbooks("Try.xlsm").Activate
    ws.Range("A1").Copy
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Exit For
  End If
Next i
End Sub

Thanks for helping!

 

[HalfAce]

Can some help me set up how to look for the workbook and select
like Backup Data & month & i &,.xlsm etc???

Hi Pedie,
Exactly how do you want to do this?
Do you want to have the user scroll through a folder and select the file to open? or, say enter a date into a cell somewhere and use that? - or perhaps call up an inputbox to enter the date and have it use that?

What will be the variable in the workbook name, the entire date? or just the day? or...(?)

 

[pedie]

There are several files with similiar names like "Backup Data September 29, 2010"
User may open any of these files and the name of the file will always be "Backup Data then month then date (which may changes), 2010(this will always be 2010).xlsm
So when the user run the code say from book1, it looks all the workbooks opened and then the look for name.. or let the user just enter the date in the pop up like 29
Thanks again Dan!

 

[pedie]

Can we make the code look for file name "Backup Data & Month & date(Input box), & Year.xlsm if that is easier

 

[pedie]

Can i make the workbook name we = A1.value? So that a person before running the code can make change the number of the date???

<TABLE style="WIDTH: 499pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=664 border=0><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 192pt; mso-width-source: userset; mso-width-alt: 9362" width=256><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2998" width=82><COL style="WIDTH: 53pt; mso-width-source: userset; mso-width-alt: 2560" width=70><COL style="WIDTH: 48pt" span=3 width=64><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD class=xl70 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=64 height=17> </TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 192pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=256>A</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 62pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=82>B</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 53pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=70>C</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>D</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>E</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>F</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>1</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">=D14&" "&E14&" "&F14&""&G14&""&H14</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">Data Backup</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">September</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">29</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">, 2010</TD><TD class=xl74 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">.xlsm</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl75 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: transparent" height=18> </TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Data Backup September 27, 2010.xlsm</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">Data Backup</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">September</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">27</TD><TD class=xl76 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">, 2010</TD><TD class=xl77 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: transparent">.xlsm</TD></TR></TBODY></TABLE>

 

[pedie]

I am trying this way but since I dont know how to put it together it is not working

Sub try121()
Dim wb As Workbook
Dim wb1 As String
wb1 = Sheets("Data").Range("A1").Value

Windows"wb1").Activate
Sheets("Dater").Select
LstRw = Cells(Rows.Count, "O").End(xlUp).Row
  Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"
  Range("B2:B" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
   ThisWorkbook.Sheets("Data").Cells(Rows.Count, "G").End(xlUp)(2)
  AutoFilterMode = False
ThisWorkbook.Activate
End Sub

 

[HalfAce]

Can we make the code look for file name "Backup Data & Month & date(Input box), & Year.xlsm if that is easier

Sorry for the delay. (Work rearing its ugly head again.)
This is not tested (read comment above...) but does something like this help at all?

BookDate = InputBox("Please enter the date of the file to open")
If Len(BookDate) = 0 Then Exit Sub
If Not IsDate(BookDate) Then MsgBox "'" & BookDate & "' is not a valid date.": Exit Sub
BookToOpen = "Backup Data " & Format(BookDate, "mmmm d yyyy") & ".xls"

MsgBox "The file I want to open is:  " & BookToOpen: Exit Sub

That help at all?

 

[pedie]

That was very close Dan..., now i have shorten the name to just "Month date, year.xlsm" so the number that is written in inputbox will be date number...I am sure you are very busy but please take a look again.
so suppose i enter 20 in input box then the name of the file will look like this "September 20, 2010.xlsm".

Could u please redesign the code for me???

P.S: there is a coma after the date number


Thanks again for helping!

Anyone, please suggest

 

[pedie]

when i use this why is the year showing 1905?

BookDate = InputBox("Please enter the date of the file to open")
Cells(1).Value = MonthName(Month(Now)) & " " & BookDate & ", " & Year(Now) & ".xlsm"

sorry got it now...
now how do i fix both the code together???

Sub try121()
 
Dim wb As Workbook
 
BookDate = InputBox("Please enter the date of the file to open")
Cells(1).Value = MonthName(Month(Now)) & " " & BookDate & ", " & Year(Now) & ".xlsm"
 
Windows(BookDate).Activate
Sheets("Dater").Select
LstRw = Cells(Rows.Count, "O").End(xlUp).Row
Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"
Range("B2:B" & LstRw).SpecialCells(xlCellTypeVisible).Copy _
ThisWorkbook.Sheets("Data").Cells(Rows.Count, "G").End(xlUp)(2)
AutoFilterMode = False
ThisWorkbook.Activate
End Sub

 

[meldoc]

Hi Pedie

This is the code that HalfAce, gave you modified to your request in post #5/#6, you were missing a path, I used the one from the last thread.
Also notice that I have changed the formula, to remove some blanks.

Excel Workbook
	A	B	C	D	E	F	G
1	D:\Data Backup September 29, 2010.xlsm	D:\	Data Backup	September	29	, 2010	.xlsm
Data

Excel 2010

Cell Formulas
Range	Formula
A1	=B1&C1&" "&D1&" "&E1&F1&G1

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> try121()<br><br>MyFile = Sheets("Data").Range("A1").Value<br><SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">Resume</SPAN><SPAN style="color:#00007F">Next</SPAN><br><SPAN style="color:#00007F">Set</SPAN> wb1 = Workbooks.Open(Filename:=MyFile)<br><SPAN style="color:#00007F">On</SPAN><SPAN style="color:#00007F">Error</SPAN><SPAN style="color:#00007F">GoTo</SPAN> 0<br><SPAN style="color:#00007F">If</SPAN> wb1<SPAN style="color:#00007F">Is</SPAN><SPAN style="color:#00007F">Nothing</SPAN><SPAN style="color:#00007F">Then</SPAN> MsgBox "Error Occured, file not found":<SPAN style="color:#00007F">Exit</SPAN><SPAN style="color:#00007F">Sub</SPAN><br><br><br>LstRw = Cells(Rows.Count, "O").End(xlUp).Row<br>  Range("O1:O" & LstRw).AutoFilter Field:=1, Criteria1:="Y"<br>  Range("B2:B" & LstRw).SpecialCells(xlCellTypeVisible).Copy _<br>   ThisWorkbook.Sheets("Data").Cells(Rows.Count, "G").End(xlUp)(2)<br>  wb1.ActiveSheet.AutoFilterMode =<SPAN style="color:#00007F">False</SPAN><br>ThisWorkbook.Activate<br><SPAN style="color:#00007F">End</SPAN><SPAN style="color:#00007F">Sub</SPAN></FONT>