Solved: Syntax Question

[mercmannick]

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]

Assuming that VBAWeeknum is a function in your project, the only thing I can see is a possible extra space

[vba]

Workbooks("KPI 0044 wk" & CStr(VBAWeekNum(Now(), 1)) & " internal Shortages" & ".xls")
[/vba]

[mercmannick]

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]

'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]

Dir command is the simplest check
[VBA]
If Len(Dir("C:\Test.xls")) = 0 Then MsgBox "File not found"

[/VBA]

[mercmannick]

yes but how would you stop sub if not found

Merc

[mdmackillop]

[VBA]
If Len(Dir("C:\Test.xls")) = 0 Then
MsgBox "File not found"
Exit Sub
End If

[/VBA]

[Norie]

Merc

If your still having trouble with the space don't use CStr, you don't need it.

[mercmannick]

Workbooks("KPI 0044 wk" & (VBAWeekNum(Now(), 1)) & _
 " internal Shortages" & ".xls")

Norie so this will work like this

Merc

[mercmannick]

wbEXT = "C:\KPI 0015 wk" & (VBAWeekNum(Now(), 1)) & " external Shortages" & (".xls")

Cool works a treat thanks

Merc

[mdmackillop]

You don't actually need all the brackets.
[VBA]

wbEXT = "C:\KPI 0015 wk" & VBAWeekNum(Now(), 1) & " external Shortages" & ".xls"

[/VBA]

[mercmannick]

cool thnx md

Merc