|
VBA - MS VB Compile Error
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 OLEDB:Database Password="""";Jet OLEDB:Engine Type=35;Je OLEDB:Database 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 OLEDB:Don'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 -- jordanct ----------------------------------------------------------------------- jordanctc's Profile: http://www.excelforum.com/member.php...nfo&userid=676 View this thread: http://www.excelforum.com/showthread.php?threadid=26617 |
VBA - MS VB Compile Error
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. -- Regards, Tom Ogilvy "jordanctc" wrote in message ... 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 |
VBA - MS VB Compile Error
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 |
VBA - MS VB Compile Error
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 |
VBA - MS VB Compile Error
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 |
| All times are GMT +1. The time now is 12:00 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com