PDA

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