Excel VBA worked in 2003 not in 2007

[m1k3j]

Option Explicit
Option Base 1
Dim FldNames As Variant
Dim DptNames As Variant
Dim fpath As String
Dim fper As String
Dim i As Integer
Sub OpenChicago()
FldNames = Array("grocery", "liquor", _
"gm", "produce", "nutrition", "meat", _
"srvdeli", "bakery\isbakery")
DptNames = Array("groc", "liquor", _
"gm", "produce", "nutrition", "meat", _
"srvdeli", "isbakery")

fpath = ThisWorkbook.Worksheets("A").Range("pathmw").Value
fper = ThisWorkbook.Worksheets("A").Range("C6").Value

For i = 1 To 8
Workbooks.Open Filename:=fpath '& FldNames(i) & "\" & DptNames(i) & fper, UpdateLinks:=1, _
IGNOREREADONLYRECOMMENDED:=True
Next i
MsgBox ("All margin files are now opened.")
End Sub

This code works fine in 2003 but in 2007 it stops at the Workbooks.open line with a cannot find path error. Any ideas.
Thank you

 

[Weaver]

is the last 2/3 of that line supposed to be commented out?

 

[m1k3j]

No my bad, I was messing around trying to figure this out. Those are not there in the correct code.

 

[m1k3j]

This is the correct code...That works in 2003 but not in 2007
Option Explicit
Option Base 1
Dim FldNames As Variant
Dim DptNames As Variant
Dim fpath As String
Dim fper As String
Dim i As Integer
Sub OpenChicago()
FldNames = Array("grocery", "liquor", _
"gm", "produce", "nutrition", "meat", _
"srvdeli", "bakery\isbakery")
DptNames = Array("groc", "liquor", _
"gm", "produce", "nutrition", "meat", _
"srvdeli", "isbakery")

fpath = ThisWorkbook.Worksheets("A").Range("B6").Value
fper = ThisWorkbook.Worksheets("A").Range("C6").Value

For i = 1 To 8
Workbooks.Open Filename:=fpath & FldNames(i) & "\" & DptNames(i) & fper, updatelinks:=1, IGNOREREADONLYRECOMMENDED:=True
Next i
MsgBox ("All margin files are now opened.")
End Sub

 

[CircuitsMan]

What is stored in Cell B6?

I came across some code not too long ago that used Application.Path and/or Application.LibraryPath to return the file directory/location of the Office folder. On my computer, these return.

C:\Program Files\Microsoft Office\Office12
C:\Program Files\Microsoft Office\Office12\LIBRARY

I don't have Excel 2003, but I think it links to a different folder like Office11.

 

[m1k3j]

That cell is on the worksheet and has the direction to the drive and folder from where I need the other workbooks opened..
ie:<TABLE style="WIDTH: 125pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=167><COLGROUP><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6107" width=167><TBODY><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 125pt; HEIGHT: 25.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=34 width=167>J:\xxChicago_f4l\2009\</TD></TR></TBODY></TABLE>

 

[m1k3j]

I figured it out on my own. I guess office 2007 is no longer a .xls ext so you have to add .xls to each reference of the file trying to open.