Access To Excel Entire Column

[DMT Dave]

Hi Guy's, i am so close with this if anyone can help, the following outputs an update in Excel and testing the BOLD line converts the cells (3, 9) to dd-mm-yy perfect, can i do the entire column from 3 ,9 by rs.recordCount (therefore row 3 to 690) if rs.recordCount is 690 ?

mStart = Format(Date - Weekday(Date) + 1, "mm/dd/yyyy")
mEnd = Format(Date + 7 - Weekday(Date), "mm/dd/yyyy")


Set ApXL = CreateObject("Excel.Application")
Set XLWb = ApXL.Workbooks.Add
intSheets = XLWb.Worksheets.Count
Set xlSheetLast = XLWb.Worksheets(intSheets)
Set xlSheet = XLWb.Worksheets.Add(, xlSheetLast, 1, xlWorksheet)
ApXL.ActiveWorkbook.SaveAs (strPathName)
Set XLWb = ApXL.Workbooks.Open(strPathName)
ApXL.Visible = True
Set XlWs1 = XLWb.Worksheets("Sheet1")
XlWs1.Name = "Deliveries"

Set rs = CurrentDb.OpenRecordset("SELECT tblAssign.SONumber, tblAssign.DelTo, tblAssign.PONumber, tblAssign.LiftType, tblAssign.LiftNo, tblAssign.Status, tblAssign.DeliveryDate FROM tblAssign WHERE DeliveryDate Between #" & mStart & "# AND #" & mEnd & "#" & " And Customer = '" & Cust & "'" & " And Source = '" & Src & "'" & " ORDER BY DeliveryDate DESC")

Set XLWb = ApXL.Workbooks.Open(strPathName)


With XLWb
.Worksheets(1).Cells(1) = "PRESS AND HOLD 'CTRL'"
.Worksheets(1).Cells(2, 1) = "AND PRESS 'F' KEY"
.Worksheets(1).Cells(3, 1) = "WHEN THE SEARCH BOX APPEARS"
.Worksheets(1).Cells(4, 1) = "TYPE YOUR SEARCH"
.Worksheets(1).Cells(1, 3) = "SL-NUMBER"
.Worksheets(1).Cells(1, 4) = "DEALER"
.Worksheets(1).Cells(1, 5) = "PO-NUMBER"
.Worksheets(1).Cells(1, 6) = "LIFT TYPE"
.Worksheets(1).Cells(1, 7) = "LIFT DETAILS"
.Worksheets(1).Cells(1, 8) = "STATUS"
.Worksheets(1).Cells(1, 9) = "DELIVERY"
.Worksheets(1).Cells(3, 3).CopyFromRecordset rs
.Worksheets(1).Range("C3:I3").Borders(xlEdgeTop).L ineStyle = xlContinuous
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignm ent = xlLeft
.Worksheets(1).Cells(3, 9).NumberFormat = "dd-mm-yy" WORKS PERFECT, THE REST OF COLUMN 9 is still NUMBERS

Can i do something like .Cells(3, 9).Range (rs.recordCount) ????


Much appreciated

[ranman256]

set the whole column
.Worksheets(1).columns("I:I").NumberFormat =...

[DMT Dave]

Excellent thank you, that worked a treat, another question, is it easy for Access to add a command button with an input box to search a criteria instead of

.Worksheets(1).Cells(1) = "PRESS AND HOLD 'CTRL'"
.Worksheets(1).Cells(2, 1) = "AND PRESS 'F' KEY"
.Worksheets(1).Cells(3, 1) = "WHEN THE SEARCH BOX APPEARS"
.Worksheets(1).Cells(4, 1) = "TYPE YOUR SEARCH"
.Worksheets(1).Cells(1, 3) = "SL-NUMBER"

for example Worksheets(1).Cells(2, 1) = "AND PRESS 'F' KEY" replaced with

Worksheets(1).Cells(2, 1) = Command button

then command button onClick >>

>> Dim MyInput as String
MyInput = InputBox("Enter Your Search Criteria ?","ENTER SEARCH")

DoCmd.Find (myInput)

?

[DMT Dave]

Is there a method that .Worksheets(1).Cells(3, 1) = "WHEN THE SEARCH BOX APPEARS" can be replaced with something like:

.Worksheets(1).Cells(3, 1) = Application.CommandBars("Edit").Controls("Find") ??

[Micron]

You want Access code to add a command button to a worksheet?? That's an odd one for which I won't even ask why rather than using a properly designed worksheet, template or not
I suspect it's something you're going to have to Google, as did I.
https://stackoverflow.com/questions/...-to-a-userform