View Full Version : Solved: Syntax Question
mercmannick
08-12-2006, 11:03 AM
hi
Workbooks("KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) & ".xls")
and line
wbEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))
how can i change this to a diffrent permitation ie
Workbooks("KPI 0044 wkno here internal Shortages" & CStr(VBAWeekNum(Now(), 1)) & ".xls")
is it just as simple as
Workbooks ("KPI 0044 wk" & CStr(VBAWeekNum(Now(), 1)) & "internal Shortages" & ".xls")
Thanks
Merc
Bob Phillips
08-12-2006, 11:30 AM
Assuming that VBAWeeknum is a function in your project, the only thing I can see is a possible extra space
Workbooks("KPI 0044 wk" & CStr(VBAWeekNum(Now(), 1)) & " internal Shortages" & ".xls")
mercmannick
08-12-2006, 11:44 AM
Xld
yes vbaweeknum is a function in project
for some reason VBA is putting that space in
what i am asking if the weeknumber is in a diffrent place to
Workbooks("KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) & ".xls")
maybe like
below
"KPI 0044 wk** internal Shortages"
how would i code this
Thanks
Merc
mercmannick
08-12-2006, 11:50 AM
'open most recent book by weeknumber
wbEXT = "C:\KPI 0015 external Shortageswk" & CStr(VBAWeekNum(Now(), 1))
'open most recent book by weeknumber
wbINT = "C:\KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1))
Workbooks.Open Filename:=wbINT 'open kpi 0044
Workbooks.Open Filename:=wbEXT 'open kpi 0015
Also if there wasnt a file with the right weeeknumber how could i add that a msgbox says "WkNo** is not Available"
then exit Sub
Merc
mdmackillop
08-12-2006, 12:48 PM
Dir command is the simplest check
If Len(Dir("C:\Test.xls")) = 0 Then MsgBox "File not found"
mercmannick
08-12-2006, 03:31 PM
yes but how would you stop sub if not found
Merc
mdmackillop
08-12-2006, 04:28 PM
If Len(Dir("C:\Test.xls")) = 0 Then
MsgBox "File not found"
Exit Sub
End If
Norie
08-13-2006, 08:43 AM
Merc
If your still having trouble with the space don't use CStr, you don't need it.
mercmannick
08-13-2006, 08:51 AM
Workbooks("KPI 0044 wk" & (VBAWeekNum(Now(), 1)) & _
" internal Shortages" & ".xls")
Norie so this will work like this
Merc
mercmannick
08-13-2006, 08:56 AM
wbEXT = "C:\KPI 0015 wk" & (VBAWeekNum(Now(), 1)) & " external Shortages" & (".xls")
Cool works a treat thanks
Merc
mdmackillop
08-13-2006, 09:24 AM
You don't actually need all the brackets.
wbEXT = "C:\KPI 0015 wk" & VBAWeekNum(Now(), 1) & " external Shortages" & ".xls"
mercmannick
08-13-2006, 09:53 AM
cool thnx md
Merc
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.