Nice catch. I guess I got caught nappin. References is a more likely cause of
a compile error...

"Tom Ogilvy" wrote:

Just some added thoughts.
What you describe would cause a runtime error, not a compile error. For
example,

Sub DDD()
Workbooks.Open "\\ThatThing\Thisthing\Accounting\..."
End Sub

compiles fine.

As does:

Sub DDDD()
Workbooks.Open "J:\ThatThing\Thisthing\Accounting\..."
End Sub

and I don't have a j drive.

--
Regards,
Tom Ogilvy

"Jim Thomlinson" wrote in message
...
You are using the local drive mapping instead of the full URL.
"I:\Accounting\..." some computers may not have that same mapping. The
best
wat ot do this is to use the full URL. In windows explorer you will see
your
I drive. Beside it will be the actual mapping. "ThisThing on ThatThing".
Change the path to "\\ThatThing\Thisthing\Accounting\..."

As an aside it is best to make the Path into a constant at the beginning
of
the module and then just refer to the constant in the code. This is not
necessary, but it is a really good idea.

Hope this helps...

"jordanctc" wrote:


The following code was working on Friday but today on several computers
the VBA code has a big problem with the format function. I am
getting:

Microsoft Visual Basic

Compile Error

Wrong number of arguements or invalid property assignment.


With ActiveSheet.QueryTables.Add(Connection:=Array( _
"OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;Password=" """;User
ID=Admin;Data Source=I:\Accounting\Inventory Files\" & intYear &
"\Inventory " & Format(datDate, "m-dd-yyyy") & " wi" _
, _
"th Discounts.xls;Mode=Share Deny Write;Extended
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Jet
OLEDB:Registry Path="""";Je" _
, _
"t OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Jet
OLEDB:Database Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet "
_
, _
"OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Database
Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encrypt
Dat" _
, _
"abase=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet
OLEDB:Compact Without Replica Repair=False;Jet OLEDB:SFP=False" _
), Destination:=Range("A1"))
.CommandType = xlCmdTable
.CommandText = Array("'Accounting EOM Inventory Report$'")
.Name = "Inventory " & Format(datDate, "m-dd-yyyy") & " with
Discounts"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.SourceDataFile = _
"I:\Accounting\Inventory Files\" & intYear & "\Inventory " &
Format(datDate, "m-dd-yyyy") & " with Discounts.xls"
.Refresh BackgroundQuery:=False
End With


Any help is appreciated,
Jordan


--
jordanctc
------------------------------------------------------------------------
jordanctc's Profile:
http://www.excelforum.com/member.php...fo&userid=6761
View this thread:
http://www.excelforum.com/showthread...hreadid=266175