VBA - MS VB Compile Error

[jordanctc]

The following code was working on Friday but today on several computer
the VBA code has a big problem with the format function. I a
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="""";Use
ID=Admin;Data Source=I:\Accounting\Inventory Files\" & intYear
"\Inventory " & Format(datDate, "m-dd-yyyy") & " wi" _
, _
"th Discounts.xls;Mode=Share Deny Write;Extende
Properties=""HDR=YES;"";Jet OLEDB:System database="""";Je
OLEDB:Registry Path="""";Je" _
, _
"t OLEDBatabase Password="""";Jet OLEDB:Engine Type=35;Je
OLEDBatabase Locking Mode=0;Jet OLEDB:Global Partial Bulk Ops=2;Jet
_
, _
"OLEDB:Global Bulk Transactions=1;Jet OLEDB:New Databas
Password="""";Jet OLEDB:Create System Database=False;Jet OLEDB:Encryp
Dat" _
, _
"abase=False;Jet OLEDBon't Copy Locale on Compact=False;Je
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") & " wit
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,
Jorda

 

[Tom Ogilvy]

on the problematic computers, go into the vbe and with this workbook as the
active project (highlighted in the project explorer) go to Tools=>References
and see if you have any references marked as MISSING. If so, if they are
not being used, you need to remove them. If they are being used, you need
to fix them using the browse button.

 

[Guest]

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...

 

[Tom Ogilvy]

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.

 

[Guest]

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