RE: Recording a Macro

Tech-Archive recommends: Speed Up your PC by fixing your registry

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 spread***. 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 Active***.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\Documents and Settings\george.coy\My
Documents\QualityReports\QualityFront.mdb;DefaultDir=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;SafeTransactions=0;SystemDB=C:\Documents and Settings\george.coy\My Documents\Qualit" _
),
Array("yReports\QualityDb\QUALITY.MDW;Threads=3;UID=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
.

Quantcast