Excel Options From Access

[DMT Dave]

Hi All, does anyone know how to add a command button to Cells("1:1") to an Excel work sheet ? and program the command button with an input box to search text ?, i have done lots of these in Access but not tried to generate to Excel

ie:

[Dim mInput, mYear As LongDim ApXL, XLWb, XlWs1, xlWs2, xlWs3, xlWs4, xlWs5 As Object
Dim wsAdd1, wsAdd2 As Worksheet
Dim strPathName, mAsc, Cust, Src As String
Dim rs, rs2, rs3, rs4, rs5 As DAO.Recordset
Dim mStart, mEnd As Date


mStart = "01/01/2018"
mEnd = "31/12/2018"
strPathName = "T:" & "DMT DELIVERIES" & ".xlsx"
Cust = "MyCustomer"
Src = "Acc"
mYear = DatePart("yyyy", Me.txtUpdate)
Set ApXL = CreateObject("Excel.Application")
Set XLWb = ApXL.Workbooks.Add
ApXL.Visible = True
Set XlWs1 = XLWb.Worksheets("Sheet1")
XlWs1.Name = mYear & "-" & "DELIVERIES"
Set xlWs2 = XLWb.Worksheets("Sheet2")
xlWs2.Name = "COLLECTIONS"
ApXL.ActiveWorkbook.SaveAs (strPathName)
Set rs = CurrentDb.OpenRecordset("SELECT tblAssign.DelTo, tblAssign.DeliveryDate, tblAssign.SONumber FROM tblAssign WHERE DeliveryDate Between #" & mStart & "# And #" & mEnd & "#" & " And Customer = '" & Cust & "'" & " And SOURCE = '" & Src & "' ORDER BY DeliveryDate Desc")
Set rs2 = CurrentDb.OpenRecordset("SELECT tblCollections.DelTo, tblCollections.CollectedDate, tblCollections.SONumber FROM tblCollections WHERE Customer = '" & Cust & "'" & " And SOURCE = '" & Src & "' ORDER BY CollectedDate DESC")
Set XLWb = ApXL.Workbooks.Open(strPathName)
With XLWb
.Worksheets(1).Cells(1, 1) = COMMAND BUTTON inputbox("Enter SL-Number ?","SEARCH SL-NUMBER")
Worksheets(1).Cells(1, 3) = "DEALER"
.Worksheets(1).Cells(1, 4) = "DELIVERY DATE"
.Worksheets(1).Cells(1, 5) = "SL-NUMBER"
.Worksheets(1).Cells(3, 3).CopyFromRecordset rs
.Worksheets(1).Cells.EntireColumn.AutoFit
.Worksheets(1).Cells.EntireColumn.HorizontalAlignm ent = xlLeft
.Worksheets(1).Range("C3:C6").Borders(xlEdgeBottom ).LineStyle = xlContinuous
.Worksheets(2).Cells(1).CopyFromRecordset rs2
.Worksheets(2).Cells.EntireColumn.AutoFit


.Worksheets(2).Cells.EntireColumn.HorizontalAlignm ent = xlLeft
.Save
End With]

OR INSTEAD OF GENERATING A NEW XL SHEET EVERY MORNING, KEEP THE SAME ONE WITH A COMMAND BUTTON INPUT BOX AND UPDATE CELLS ("1:3") / ("1:4") and ("1:5")

Many Thanks

Dave

[Micron]

Can depend on the Excel version, but in 2016 on Ribbon > Developer > Insert to put a control on a sheet. You'll get a prompt to assign or create a macro. Create will open the vb editor. The related method would be to create what is called a userForm (not form) in Excel. To keep using the same sheet, you'll have to edit an existing one rather than create, or use one as a template to create new ones.

As an observation the way you declare your variables on one line means that everything that's not explicitly typed (and I don't mean keyboard) is a variant.
e.g. only Src is a string, the rest are variants and you're relying on Access to correctly figure it out when values are assigned.
Dim strPathName As String, mAsc As String, Cust As String, Src As String
not
Dim strPathName, mAsc, Cust, Src As String
unless you intended them to be variants.

EDIT
Forgot to request that for more than a few lines of code, please use code tags (# on forum toolbar) along with proper indentation to make your code easier to read. Also prevents forum from adding spaces at the 50 character mark, which only adds to confusion in code posts.

[DMT Dave]

Hi Micron, much appreciated, will give those changes a go, i have done a lot in access VBA but not excel!!