I am recording this macro and when I try to run it the second time I get a
Syntax error message. The interesting thing is, I have recorded this macro
several times and the error message is not alway the same. Originally it was
erroring on a fragment of code where "ch(14) & ch(10)" saying it couldn't
find the Project Library. What is missing? The macro works when I initially
record it, but when I try to replay it, it fails.

Cells.Select
Selection.ClearContents
Range("B4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DBQ=C:\Documents and Settings\george.coy\My
Documents\QualityReports\QualityFront.mdb;DefaultD ir=C:\Documents and
Settings\georg" _
), Array( _
"e.coy\My Documents\QualityReports;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;Exclusive=0;FIL=MS Access;MaxBufferSize=2" _
), Array( _

"048;MaxScanRows=8;PageTimeout=5;;ReadOnly=1;SafeT ransactions=0;SystemDB=C:\Documents and Settings\george.coy\My Documents\Qualit" _
),
Array("yReports\QualityDb\QUALITY.MDW;Threads=3;UI D=gcoy;UserCommitSync=Yes;"
_
)), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT ArrisWarrantyReturns.assembly, ArrisWarrantyReturns.Built,
ArrisWarrantyReturns.CRGReceivingID, ArrisWarrantyReturns.CustomerID,
ArrisWarrantyReturns.EventID, ArrisWarrantyReturns.EventTyp, Arr" _
, _
"isWarrantyReturns.ManufBy, ArrisWarrantyReturns.MfgOrderNo,
ArrisWarrantyReturns.PlxJobNo, ArrisWarrantyReturns.ProcessTyp,
ArrisWarrantyReturns.ReasonForReturn, ArrisWarrantyReturns.ReceiveDate, Arri"
_
, _
"sWarrantyReturns.RMA, ArrisWarrantyReturns.SerialNo,
ArrisWarrantyReturns.`WarrantyBySerial#`" & Chr(13) & "" & Chr(10) & "FROM
ArrisWarrantyReturns ArrisWarrantyReturns" _
)
.Name = "Query from Quality_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

CIt probably doesn't like this part of thhe code
Chr(13) & "" & Chr(10)

I don't thin you need a double quote between a charrage return (chr(13)) and
a line feed (chr(10)).

It should be just
Chr(13) & Chr(10)

If the double quotes are needed to prevent a syntax error use
Chr(13) & chr(34) & Chr(10)

"geocoy" wrote:

I am recording this macro and when I try to run it the second time I get a
Syntax error message. The interesting thing is, I have recorded this macro
several times and the error message is not alway the same. Originally it was
erroring on a fragment of code where "ch(14) & ch(10)" saying it couldn't
find the Project Library. What is missing? The macro works when I initially
record it, but when I try to replay it, it fails.

Cells.Select
Selection.ClearContents
Range("B4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DBQ=C:\Documents and Settings\george.coy\My
Documents\QualityReports\QualityFront.mdb;DefaultD ir=C:\Documents and
Settings\georg" _
), Array( _
"e.coy\My Documents\QualityReports;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;Exclusive=0;FIL=MS Access;MaxBufferSize=2" _
), Array( _

"048;MaxScanRows=8;PageTimeout=5;;ReadOnly=1;SafeT ransactions=0;SystemDB=C:\Documents and Settings\george.coy\My Documents\Qualit" _
),
Array("yReports\QualityDb\QUALITY.MDW;Threads=3;UI D=gcoy;UserCommitSync=Yes;"
_
)), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT ArrisWarrantyReturns.assembly, ArrisWarrantyReturns.Built,
ArrisWarrantyReturns.CRGReceivingID, ArrisWarrantyReturns.CustomerID,
ArrisWarrantyReturns.EventID, ArrisWarrantyReturns.EventTyp, Arr" _
, _
"isWarrantyReturns.ManufBy, ArrisWarrantyReturns.MfgOrderNo,
ArrisWarrantyReturns.PlxJobNo, ArrisWarrantyReturns.ProcessTyp,
ArrisWarrantyReturns.ReasonForReturn, ArrisWarrantyReturns.ReceiveDate, Arri"
_
, _
"sWarrantyReturns.RMA, ArrisWarrantyReturns.SerialNo,
ArrisWarrantyReturns.`WarrantyBySerial#`" & Chr(13) & "" & Chr(10) & "FROM
ArrisWarrantyReturns ArrisWarrantyReturns" _
)
.Name = "Query from Quality_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Oh that is interesting, this code is auto-generated by Excel. Why would it
use for a charrage return at all. That implys it is expecting a keyboard
response doesn't it?

By the way, I quit out of the application a came back to it the next day, I
decided to try copying a previous recorded macro that was doing the same
thing (successfully) and just change the MS Access query it was using to
populate the spreadsheet. It worked. The macros don't even resemble each
other. The one I used in this request for help lists each of the fields in
the query, while the one I got to work simply says use all the fields in this
query. Both macros are auto generated, I'm confused why Excel would do this.

Thanks

George

"Joel" wrote:

CIt probably doesn't like this part of thhe code
Chr(13) & "" & Chr(10)

I don't thin you need a double quote between a charrage return (chr(13)) and
a line feed (chr(10)).

It should be just
Chr(13) & Chr(10)

If the double quotes are needed to prevent a syntax error use
Chr(13) & chr(34) & Chr(10)

"geocoy" wrote:

I am recording this macro and when I try to run it the second time I get a
Syntax error message. The interesting thing is, I have recorded this macro
several times and the error message is not alway the same. Originally it was
erroring on a fragment of code where "ch(14) & ch(10)" saying it couldn't
find the Project Library. What is missing? The macro works when I initially
record it, but when I try to replay it, it fails.

Cells.Select
Selection.ClearContents
Range("B4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DBQ=C:\Documents and Settings\george.coy\My
Documents\QualityReports\QualityFront.mdb;DefaultD ir=C:\Documents and
Settings\georg" _
), Array( _
"e.coy\My Documents\QualityReports;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;Exclusive=0;FIL=MS Access;MaxBufferSize=2" _
), Array( _

"048;MaxScanRows=8;PageTimeout=5;;ReadOnly=1;SafeT ransactions=0;SystemDB=C:\Documents and Settings\george.coy\My Documents\Qualit" _
),
Array("yReports\QualityDb\QUALITY.MDW;Threads=3;UI D=gcoy;UserCommitSync=Yes;"
_
)), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT ArrisWarrantyReturns.assembly, ArrisWarrantyReturns.Built,
ArrisWarrantyReturns.CRGReceivingID, ArrisWarrantyReturns.CustomerID,
ArrisWarrantyReturns.EventID, ArrisWarrantyReturns.EventTyp, Arr" _
, _
"isWarrantyReturns.ManufBy, ArrisWarrantyReturns.MfgOrderNo,
ArrisWarrantyReturns.PlxJobNo, ArrisWarrantyReturns.ProcessTyp,
ArrisWarrantyReturns.ReasonForReturn, ArrisWarrantyReturns.ReceiveDate, Arri"
_
, _
"sWarrantyReturns.RMA, ArrisWarrantyReturns.SerialNo,
ArrisWarrantyReturns.`WarrantyBySerial#`" & Chr(13) & "" & Chr(10) & "FROM
ArrisWarrantyReturns ArrisWarrantyReturns" _
)
.Name = "Query from Quality_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With

Recorded Macros aren't very good. They should only be used as a boiler-plate
for creating your own macro.

"geocoy" wrote:

Oh that is interesting, this code is auto-generated by Excel. Why would it
use for a charrage return at all. That implys it is expecting a keyboard
response doesn't it?

By the way, I quit out of the application a came back to it the next day, I
decided to try copying a previous recorded macro that was doing the same
thing (successfully) and just change the MS Access query it was using to
populate the spreadsheet. It worked. The macros don't even resemble each
other. The one I used in this request for help lists each of the fields in
the query, while the one I got to work simply says use all the fields in this
query. Both macros are auto generated, I'm confused why Excel would do this.

Thanks

George

"Joel" wrote:

CIt probably doesn't like this part of thhe code
Chr(13) & "" & Chr(10)

I don't thin you need a double quote between a charrage return (chr(13)) and
a line feed (chr(10)).

It should be just
Chr(13) & Chr(10)

If the double quotes are needed to prevent a syntax error use
Chr(13) & chr(34) & Chr(10)

"geocoy" wrote:

I am recording this macro and when I try to run it the second time I get a
Syntax error message. The interesting thing is, I have recorded this macro
several times and the error message is not alway the same. Originally it was
erroring on a fragment of code where "ch(14) & ch(10)" saying it couldn't
find the Project Library. What is missing? The macro works when I initially
record it, but when I try to replay it, it fails.

Cells.Select
Selection.ClearContents
Range("B4").Select
With ActiveSheet.QueryTables.Add(Connection:=Array(Arra y( _
"ODBC;DBQ=C:\Documents and Settings\george.coy\My
Documents\QualityReports\QualityFront.mdb;DefaultD ir=C:\Documents and
Settings\georg" _
), Array( _
"e.coy\My Documents\QualityReports;Driver={Microsoft Access Driver
(*.mdb)};DriverId=25;Exclusive=0;FIL=MS Access;MaxBufferSize=2" _
), Array( _

"048;MaxScanRows=8;PageTimeout=5;;ReadOnly=1;SafeT ransactions=0;SystemDB=C:\Documents and Settings\george.coy\My Documents\Qualit" _
),
Array("yReports\QualityDb\QUALITY.MDW;Threads=3;UI D=gcoy;UserCommitSync=Yes;"
_
)), Destination:=Range("B4"))
.CommandText = Array( _
"SELECT ArrisWarrantyReturns.assembly, ArrisWarrantyReturns.Built,
ArrisWarrantyReturns.CRGReceivingID, ArrisWarrantyReturns.CustomerID,
ArrisWarrantyReturns.EventID, ArrisWarrantyReturns.EventTyp, Arr" _
, _
"isWarrantyReturns.ManufBy, ArrisWarrantyReturns.MfgOrderNo,
ArrisWarrantyReturns.PlxJobNo, ArrisWarrantyReturns.ProcessTyp,
ArrisWarrantyReturns.ReasonForReturn, ArrisWarrantyReturns.ReceiveDate, Arri"
_
, _
"sWarrantyReturns.RMA, ArrisWarrantyReturns.SerialNo,
ArrisWarrantyReturns.`WarrantyBySerial#`" & Chr(13) & "" & Chr(10) & "FROM
ArrisWarrantyReturns ArrisWarrantyReturns" _
)
.Name = "Query from Quality_1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With