Copy An Excel Template, Rename And Open

[Minty]

Double post !

Can you not just get your data into a recordset with the correct values in a sensible layout, and simply either loop through it (Slow), or better still simply copy from recordset into the appropriate place in the template file.


What's the layout of the template ?

That way you wouldn't need to adjust things based on the number of drops etc - just fill in the values where appropriate?

Post up the template file and your sample data, forget getting everything into a form to match the template, just the raw data one example with 5 drops one with 14?

[DMT Dave]

The Data Comes From a datasheet form, after clicking DelNo

Dim strDel As String, strDriver As String, strItem As String, strEnt As String, strExt As String, strPC As StringDim intDelNo As Integer, iJS As Integer
Dim rs As DAO.Recordset
Dim dtDelDate As Date


strPC = Me.PostCode
intDelNo = Me.DelNo
iJS = Forms!frmMainMenu!txtJS
dtDelDate = Forms!frmMainMenu!txtDelDate
strDriver = Me.Driver
Forms!frmMainMenu!txtJSDriver = Me.Driver
'this forms data coes from tblAssign once all deliveries are assigned to driver, vehicle and deldate


        'Lookup any alarm codes or premises entry and exit codes based on dealers postcode
If IsNull(strEnt = DLookup("EntryCode", "tblDealers", "[PostCode] = '" & strPC & "'")) Then
    strEnt = ""
    Else
    strEnt = DLookup("EntryCode", "tblDealers", "[PostCode] = '" & strPC & "'")
End If
If IsNull(strExt = DLookup("ExitCode", "tblDealers", "[PostCode] = '" & strPC & "'")) Then
    strExt = ""
    Else
    strExt = DLookup("ExitCode", "tblDealers", "[PostCode] = '" & strPC & "'")
End If
        'changes the name of item if more than 1 then adds a letter s on the end
If Me.TotalQty = "1" Then
    strItem = "Lift"
End If
If Me.TotalQty >= "2" Then
    strItem = "Lifts"
End If
        'places the drop number, keeps incrementing so Forms!frmMainMenu!txtTotal has got the total drops
Forms!frmMainMenu!txtTotalDrops = intDelNo
        'Updates tblJobsheets del1 details to field Del1, del2 etc based on (a blank sheet is already added hence .edit
Set rs = CurrentDb.OpenRecordset("Select * From tblJobSheets WHERE IDNumber = " & iJS)
    With rs
    .Edit
    !DelDate = dtDelDate
    !Driver = strDriver
    Select Case intDelNo
Case 1
    !Del1 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txtd1 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt1 = Me.TotalQty & " " & strItem
    !DelNo = Me.DelNo
    !CartonQty1 = Me.TotalQty & " " & strItem
    !EntCode1 = strEnt
    !ExCode1 = strExt
    Forms!frmMainMenu!txtEnt1 = strEnt
    Forms!frmMainMenu!txtExt1 = strExt
    .Update
Case 2
    !Del2 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty2 = Me.TotalQty & " " & strItem
    !EntCode2 = strEnt
    !ExCode2 = strExt
    Forms!frmMainMenu!txtd2 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt2 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt2 = strEnt
    Forms!frmMainMenu!txtExt2 = strExt
    .Update
Case 3
    !Del3 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty3 = Me.TotalQty & " " & strItem
    !EntCode3 = strEnt
    !ExCode3 = strExt
     Forms!frmMainMenu!txtd3 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt3 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt3 = strEnt
    Forms!frmMainMenu!txtExt3 = strExt
    .Update
Case 4
    !Del4 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty4 = Me.TotalQty & " " & strItem
    !EntCode4 = strEnt
    !ExCode4 = strExt
    Forms!frmMainMenu!txtd4 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt4 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt4 = strEnt
    Forms!frmMainMenu!txtExt4 = strExt
    .Update
Case 5
    !Del5 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty5 = Me.TotalQty & " " & strItem
    !EntCode5 = strEnt
    !ExCode5 = strExt
    Forms!frmMainMenu!txtd5 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt5 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt5 = strEnt
    Forms!frmMainMenu!txtExt5 = strExt
    .Update
Case 6
    !Del6 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty6 = Me.TotalQty & " " & strItem
    !EntCode6 = strEnt
    !ExCode6 = strExt
    Forms!frmMainMenu!txtd6 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt6 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt6 = strEnt
    Forms!frmMainMenu!txtExt6 = strExt
    .Update
Case 7
    !Del7 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty7 = Me.TotalQty & " " & strItem
    !EntCode7 = strEnt
    !ExCode7 = strExt
    Forms!frmMainMenu!txtd7 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt7 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt7 = strEnt
    Forms!frmMainMenu!txtExt7 = strExt
    .Update
Case 8
    !Del8 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty8 = Me.TotalQty & " " & strItem
    !EntCode8 = strEnt
    !ExCode8 = strExt
    Forms!frmMainMenu!txtd8 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt8 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt8 = strEnt
    Forms!frmMainMenu!txtExt8 = strExt
    .Update
Case 9
    !Del9 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty9 = Me.TotalQty & " " & strItem
    !EntCode9 = strEnt
    !ExCode9 = strExt
    Forms!frmMainMenu!txtd9 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt9 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt9 = strEnt
    Forms!frmMainMenu!txtExt9 = strExt
    .Update
Case 10
    !Del10 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty10 = Me.TotalQty & " " & strItem
    !EntCode10 = strEnt
    !ExCode10 = strExt
    Forms!frmMainMenu!txtd10 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt10 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt10 = strEnt
    Forms!frmMainMenu!txtExt10 = strExt
    .Update
Case 11
    !Del11 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty11 = Me.TotalQty & " " & strItem
    !EntCode11 = strEnt
    !ExCode11 = strExt
    Forms!frmMainMenu!txtd11 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt11 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt11 = strEnt
    Forms!frmMainMenu!txtExt11 = strExt
    .Update
Case 12
    !Del12 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty12 = Me.TotalQty & " " & strItem
    !EntCode12 = strEnt
    !ExCode12 = strExt
    Forms!frmMainMenu!txtd12 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt12 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt12 = strEnt
    Forms!frmMainMenu!txtExt12 = strExt
    .Update
Case 13
    !Del13 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty13 = Me.TotalQty & " " & strItem
    !EntCode13 = strEnt
    !ExCode13 = strExt
    Forms!frmMainMenu!txtd13 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt13 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt13 = strEnt
    Forms!frmMainMenu!txtExt13 = strExt
    .Update
Case 14
    !Del14 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    !DelNo = Me.DelNo
    !CartonQty14 = Me.TotalQty & " " & strItem
    !EntCode14 = strEnt
    !ExCode14 = strExt
    Forms!frmMainMenu!txtd14 = Me.DelTo & "-" & Me.Town & "-" & Me.PostCode
    Forms!frmMainMenu!txt14 = Me.TotalQty & " " & strItem
    Forms!frmMainMenu!txtEnt14 = strEnt
    Forms!frmMainMenu!txtExt14 = strExt
    .Update
    .Close
    End Select
    End With
        'refreshes datasheet forms
    Forms!frmMainMenu!frmMileageSheet.Requery
    Forms!frmMainMenu!frmMileageSheetSelect.Requery

[Minty]

Okay can you post a copy of the datasheet form? And some sample data - get rid of any sensitive info.
Again that code looks painful, you could simply have a continuous form to display the deliveries, the delivery line numbering looks a massive unnecessary complication?

[DMT Dave]

HI MInty, yes, i will, I need to look at what to remove and post a copy of the data sheet form very soon, thank you

[DMT Dave]

Minty, please forgive me, i got this working perfect, for some reason, i copied the initial template file to another folder then renamed to Mileage sheet Template.xlsx

Picked it up and worked ok!!!!

Still going to go through it and try and minimize code somewhat

[Minty]

Glad you are getting somewhere.

I think in this instance you have gone down a route that made sense in your head, but is a bit of a rabbit hole in real use/design.
Take a step back and just think about dealing with it as a set of data not individual rows. I suspect it will become a lot simpler.

[DMT Dave]

Minty, you are right, whilst a lot you guys on here have sent many examples, i know about looping within looping and using for statements, i know I can given concentration time without interruption i should be able to:

Set each field in jblJobSheets Del1 to Del14 by separating the field names to integers and start using select total drops and string the field names with integer as end of field name etc

Will play around