Macro to open workbook

[abxy]

i figure that this is kind of simple if you know the syntax for it, bu
i don't, so here's my problem:

ok, i know that if i wanted to open a file whose name was in cell A3
i'd write something like:

"C:\" & Range("A3").Value & ".xls" ...right?

ok, now i basically want to do that, except my filename is the curren
month...so how do i that? also, and most importantly what would I pu
if my file name was 1 month, or 2 months before the current month.

Thanks


oh...and i know this doesn't particularly go with the topic, but ho
would you write the code that basically says "if this workbook isn'
already open, open it

 

[Frank Kabel]

Hi
try (assumption: your files are named like 'March.xls')
sub foo()
dim fname as string
dim path as string
Dim WBook As Workbook

path = "C:\"
fname = Format(avtivesheet.range("A1").value,"mmmm") & ".xls"
On Error Resume Next
Set WBook = Workbooks(fname)
On Error GoTo 0
If WBook Is Nothing Then
Workbooks.Open Filename:= path & fname
Set WBook = Workbooks(filename)
End If
end Sub

For a different month (the previous for example) try replacing the line
fname = Format(avtivesheet.range("A1").value,"mmmm") & ".xls"

with the lines:
with avtivesheet.range("A1")
fname = format(Dateserial(Year(.value),month(.value)-1,1),"mmmm") &
".xls"

 

[Steve Garman]

one way:

Sub test()
Dim mth As Integer, fName As String
mth = Month(Date) - 4 '4 months ago
If mth < 1 Then mth = 12 + mth
fName = "c:\" & MonthName(mth) & ".xls"
Workbooks.Open fName$
End Sub

 

[Rob van Gelder]

Here's an example:

Sub test()
Const cPath = "C:\"
Dim strFile As String, wkb As Workbook

' strFile = Range("A3").Value & ".xls"

' strFile = Month(Date) & ".xls"

strFile = Month(Date) - 2 & ".xls"

' strFile = Format(DateSerial(Year(Date), Month(Date) - 3, 1), "mmmm") &
".xls"

On Error Resume Next
Set wkb = Workbooks(strFile)
If Err.Number Then Workbooks.Open cPath & strFile
On Error GoTo 0
End Sub

 

[Bob Phillips]

1) Workbooks.Open FileName:="C:\My Documents\" & Format(Date,"mmm") &
".xls"

2) Workbooks.Open FileName:="C:\My Documents\" &
Format(DateSerial(Year(Date),Month(Date)-2,Day(Date))) & ".xls"

3)
On Error Resume Next
IF Not CBool(Len(Workbooks("C:\My Documents\Testfile.xls").Name)) Then
Workbooks.Open FileName:= "C:\My Documents\Testfile.xls"
End If

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[abxy]

the method for making the current month works, but the method for pas
months isn't working

 

[Bob Phillips]

What value is it returning?

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)