Solved: Syntax Question

hi

Code:

---

Workbooks("KPI 0044 internal Shortageswk" & CStr(VBAWeekNum(Now(), 1)) & ".xls")

---

and line

Code:

---

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

Code:

---

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

---

Thanks

Merc

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]

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

Code:

---

'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

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

[/VBA]

yes but how would you stop sub if not found

Merc

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

[/VBA]

Merc

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

Code:

---

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

---

Norie so this will work like this

Merc

Code:

---

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

---

Cool works a treat thanks

Merc

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

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

[/VBA]

cool thnx md

Merc