Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. 07-12-2022, 05:32 AM #1
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365

    Copy An Excel Template, Rename And Open

    HI Guy's this one is frustrating as I know i can do this but for the life of me, i can't seem to make this work!!!!!!!!!!!!!!!!! arggghhhh !!!!!!!!!!!!!!

    I am wanting make a copy of Mileage Sheet and save to the same Folder renamed the Del Date and Vehicle Reg



    I believe i am going wrong on the initial file copying and opening, i need to close all instances when done because excel is locking

    Please help me with this one ?

    Code:
    pOpen = "T:\DMT\XL Files\Mileage Sheets\"
fOpen = "Mileage Sheet" & ".xlsx"
pSave = pOpen
fSave = "Mileage Sheet" & " " & Format(Me.txtDelDate, "dd-mmm-yyyy") & " " & Me.txtReg & ".xlsx"
 
FileCopy pOpen & fOpen, pSave & fSave
 
Set objExcelApp = CreateObject("Excel.Application")
Set wb = objExcelApp.Workbooks.Open(pSave & fSave)
objExcelApp.Workbooks.Open pSave & fSave, True, False
objExcelApp.Visible = True
With objExcelApp
 
    xlWS1.Cells(2, 3).Value = "1"
    xlWS1.Cells(4, 3).Value = "2"
    xlWS1.Cells(6, 3).Value = "3"
    xlWS1.Cells(8, 3).Value = "4"
    xlWS1.Cells(10, 3).Value = "5"
    xlWS1.Cells(12, 3).Value = "6"
    xlWS1.Cells(14, 3).Value = "7"
    xlWS1.Cells(16, 3).Value = "8"
    xlWS1.Cells(18, 3).Value = "9"
    xlWS1.Cells(20, 3).Value = "10"
    xlWS1.Cells(22, 3).Value = "11"
    xlWS1.Cells(24, 3).Value = "12"
    xlWS1.Cells(26, 3).Value = "13"
    xlWS1.Cells(28, 3).Value = "14"
   
    xlWS1.Cells(2, 4).Value = Forms!frmMainMenu!txtd1
    xlWS1.Cells(2, 6).Value = Forms!frmMainMenu!txt1
    xlWS1.Cells(4, 4).Value = Forms!frmMainMenu!txtd2
    xlWS1.Cells(4, 6).Value = Forms!frmMainMenu!txt2
    xlWS1.Cells(6, 4).Value = Forms!frmMainMenu!txtd3
etc..... etc........
xlWS1.Cells(3, 2).Value = UCase(Forms!frmMainMenu!txtJSDriver)
xlWS1.Cells(4, 2).Value = Format(Me.txtDelDate - 1, "ddd-dd-mmmm-yyyy", vbUseSystemDayOfWeek)
xlWS1.Cells(5, 2).Value = Format(Me.txtDelDate, "ddd-dd-mmmm-yyyy", vbUseSystemDayOfWeek)
xlWS1.Cells(12, 2).Value = "    YES     NO"
 

xlWS1.Cells(30, 2).Value = Format(dtDelDate, "mmm") & " " & Format(dtDelDate, "yyyy") & " " & strFuel
xlWS1.Cells(1, 5).Value = "DEL-COL"
xlWS1.Cells(1, 6).Value = "Del Items"
End With
wb.Save
 
 
Set rs = Nothing
xlWB.Close
Set apXL = Nothing
Application.FollowHyperlink pSave & fSave
    Reply With Quote Reply With Quote
  2. 07-12-2022, 07:25 AM #2
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,556
    Walk through your code Dave.

    You could enter all the lines below one at a time in the Immediate window and see what happens, if you do not want to walk your code?

    pOpen = "T:\DMT\XL Files\Mileage Sheets"
    fOpen = "Mileage Sheet" & ".xlsx"
    pSave = pOpen
    fSave = "Mileage Sheet" & " " & Format(Me.txtDelDate, "dd-mmm-yyyy") & " " & Me.txtReg & ".xlsx"

    FileCopy pOpen & fOpen, pSave & fSave
    If the file is open in Excel I get Permission Denied. Not open, copies fine.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  3. 07-12-2022, 07:30 AM #3
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    What is xlWS1? I'm not sure the With block is correct, as you don't seem to be referring to the object. I'd expect a leading dot:

    .xlWS1.Cells(2, 3).Value = "1"

    Here's some code from a working app, I cut out a lot so hopefully what remains makes sense:

    Code:
     Set xl = CreateObject("excel.application")

  FileCopy strTemplate, strFile
  xl.Workbooks.Open (strFile)
  Set xlSheet = xl.Worksheets(strSheet)
  xlSheet.Select
  Set qdfStatement = db.QueryDefs(strQuery)
  

  Set rs = qdfStatement.OpenRecordset
  
    xlSheet.range("A9").copyfromrecordset rs
    xl.Cells(6, 2) = Date
    xl.Cells(3, 2) = lngPassCount

  xl.ActiveWorkbook.Save


ExitHandler:
  On Error Resume Next
  Set rs = Nothing
  Set db = Nothing
  xl.Quit
  Set xl = Nothing
  Set xlSheet = Nothing
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  4. 07-12-2022, 07:31 AM #4
    pbaldy's Avatar
    pbaldy
    pbaldy is offline Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    22,652
    Argg, too slow copying and pasting.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com
    Reply With Quote Reply With Quote
  5. 07-12-2022, 09:28 AM #5
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365
    Frustrating because used many times to copy template files to another folder, rename and open them for editing but not understanding why it won't open
    Reply With Quote Reply With Quote
  6. 07-12-2022, 09:54 AM #6
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,556
    I was concentrating on the filecopy?
    Why are you opening the file twice with different syntax?
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  7. 07-12-2022, 10:40 AM #7
    Micron
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,423
    IMO, even if the With block is necessary (and I don't see why it is; the reference is too high in the hierarchy) the first block of repetitive writing could be?
    Code:
    n = 1
For i = 2 To 28 Step 2
  xlWS1.Cells(i, 3) = CStr(n)
n = n+1
    The object that a With block pertains to ought to be the object you work with. In the posted code, the With block pertains to the application object, to which none of the operations apply. The operations apply to what looks like a worksheet.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.
    Reply With Quote Reply With Quote
  8. 07-12-2022, 11:20 AM #8
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365
    Thanks guys, I will have a play around with your suggestions, fair point WGM, i will look at not opening until copy and rename created

    I like Micron suggestion also, what is the step 2 ? is that your instruction/suggestion or is that implementing 2 cells per update ?

    I have so much code that could be tidied and that would help, initially this procedure was too large to run but shortened it quite a lot but can really go to town with is by using FOR and To Cell numbers.

    Please can i ask what is converting in the cStr is that from numeric to text ?
    Reply With Quote Reply With Quote
  9. 07-12-2022, 11:29 AM #9
    Welshgasman
    Welshgasman is online now VIP
    Windows 10 Access 2007
    Join Date
    Jan 2017
    Location
    Swansea,South Wales,UK
    Posts
    6,556
    Thanks guys, I will have a play around with your suggestions, fair point WGM, i will look at not opening until copy and rename created
    No I am taking about the two open lines after setting the excel object.
    Please use # icon on toolbar when posting code snippets.
    Cross Posting: https://www.excelguru.ca/content.php?184
    Debugging Access: https://www.youtube.com/results?sear...bug+access+vba
    Reply With Quote Reply With Quote
  10. 07-12-2022, 12:05 PM #10
    Micron
    Micron is offline Very Inert Person
    Windows 10 Access 2016
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    13,423
    Step 2 because your row reference is 2 then 4 then 6 then...
    or you could research such things as it would be good practice in terms of devising your own search terms, which also shows you what other people are asking, which further embellishes your learning. The down side might be the rabbit holes you can end up in, but that's what book marks are good for.

    Your values are text ("1"). A counter is numeric. 1 would have to be converted to "1" if that's important, which is the assumption I made.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.
    Reply With Quote Reply With Quote
  11. 07-12-2022, 12:17 PM #11
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365
    ahh ok thank you, makes sense what you are saying, just an after thought, before setting any XL objects, then use file copy, is the Name As option a more reliable option than file copy ?

    Then start setting objects, transfer data etc ?

    or rather than setting objects (excel in this case) would be more trouble free to use Application.followhyperlink as doesn't that use the file extensions native program

    Is this a better option even though i will follow all of your guidance offered to conclude the result ?

    Much appreciated as always
    Reply With Quote Reply With Quote
  12. 07-13-2022, 04:43 AM #12
    Minty
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    I wouldn't use file copy at all, I would open the template file then simply save as your new desired file name, this changes the xl object automatically to the file you want to use.
    Something like this;

    Code:
        Dim xlApp            As Object        'Excel.Application
    Dim xlWb             As Object        'Workbook
    Dim xlWS             As Object        'Worksheet
 
    Dim sTemplate         As String
    Dim sDestFile         As String   
       
    sTemplate = "T:\DMT\XL Files\Mileage Sheets\Mileage Sheet.xlsx"
    sDestFile = "T:\DMT\XL Files\Mileage Sheets\Mileage Sheet_" & Format(Me.txtDelDate, "dd-mmm-yyyy") & "_" & Me.txtReg & ".xlsx"
    
    ' Create the instance of Excel that we will use to open the template
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.workbooks.Open(sTemplate)
     ' Now save the template with the new file name and we can work with it
    xlWB.SaveAs sDestFile
    xlApp.Visible = True
    
    '''' Now do the rest of your stuff
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓
    Reply With Quote Reply With Quote
  13. 07-13-2022, 08:31 AM #13
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365
    Thank you minty, will play around
    Reply With Quote Reply With Quote
  14. 07-13-2022, 09:23 AM #14
    DMT Dave
    DMT Dave is offline VIP
    Windows 10 Access 2016
    Join Date
    May 2018
    Posts
    1,365
    Hi Minty, still having trouble with this, the db freezes then when i go to task manager, i have to end task of any excels open, it cant find the file but snipped the path to confirm the file is there!!! i'm totally lost arrghhh i should find this easy to achieve

    Have adapted your ws (worksheet) to xlWS1 to prevent me changing all of the outputs

    Is there a quicker method than having all of this code also ?

    but main thing is, the mileage sheet does exist but db freezing

    Click image for larger version.  Name: Capture.JPG  Views: 8  Size: 51.6 KB  ID: 48311

    Code:
     Dim xlApp            As Object        'Excel.Application    Dim xlWb             As Object        'Workbook
    Dim xlWS1             As Object        'Worksheet
    
    Dim sTemplate         As String
    Dim sDestFile         As String
       
    sTemplate = "T:\DMT\XL Files\Mileage Sheets\Mileage Sheet.xlsx"
    sDestFile = "T:\DMT\XL Files\Mileage Sheets\Mileage Sheet_" & Format(Me.txtDelDate, "dd-mmm-yyyy") & "_" & Me.txtReg & ".xlsx"
    
    ' Create the instance of Excel that we will use to open the template
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(sTemplate)
     ' Now save the template with the new file name and we can work with it
    xlWb.SaveAs sDestFile
    Set xlWS1 = xlWb.Sheets("Sheet1")
    xlApp.Visible = True
    
    '''' Now do the rest of your stuff


    xlWS1.Cells(2, 3).Value = "1"
    xlWS1.Cells(4, 3).Value = "2"
    xlWS1.Cells(6, 3).Value = "3"
    xlWS1.Cells(8, 3).Value = "4"
    xlWS1.Cells(10, 3).Value = "5"
    xlWS1.Cells(12, 3).Value = "6"
    xlWS1.Cells(14, 3).Value = "7"
    xlWS1.Cells(16, 3).Value = "8"
    xlWS1.Cells(18, 3).Value = "9"
    xlWS1.Cells(20, 3).Value = "10"
    xlWS1.Cells(22, 3).Value = "11"
    xlWS1.Cells(24, 3).Value = "12"
    xlWS1.Cells(26, 3).Value = "13"
    xlWS1.Cells(28, 3).Value = "14"
    
    xlWS1.Cells(2, 4).Value = Forms!frmMainMenu!txtd1
    xlWS1.Cells(2, 6).Value = Forms!frmMainMenu!txt1
    xlWS1.Cells(4, 4).Value = Forms!frmMainMenu!txtd2
    xlWS1.Cells(4, 6).Value = Forms!frmMainMenu!txt2
    xlWS1.Cells(6, 4).Value = Forms!frmMainMenu!txtd3
    xlWS1.Cells(6, 6).Value = Forms!frmMainMenu!txt3
    xlWS1.Cells(8, 4).Value = Forms!frmMainMenu!txtd4
    xlWS1.Cells(8, 6).Value = Forms!frmMainMenu!txt4
    xlWS1.Cells(10, 4).Value = Forms!frmMainMenu!txtd5
    xlWS1.Cells(10, 6).Value = Forms!frmMainMenu!txt5
    xlWS1.Cells(12, 4).Value = Forms!frmMainMenu!txtd6
    xlWS1.Cells(12, 6).Value = Forms!frmMainMenu!txt6
    xlWS1.Cells(14, 4).Value = Forms!frmMainMenu!txtd7
    xlWS1.Cells(14, 6).Value = Forms!frmMainMenu!txt7
    xlWS1.Cells(16, 4).Value = Forms!frmMainMenu!txtd8
    xlWS1.Cells(16, 6).Value = Forms!frmMainMenu!txt8
    xlWS1.Cells(18, 4).Value = Forms!frmMainMenu!txtd9
    xlWS1.Cells(18, 6).Value = Forms!frmMainMenu!txt9
    xlWS1.Cells(20, 4).Value = Forms!frmMainMenu!txtd10
    xlWS1.Cells(20, 6).Value = Forms!frmMainMenu!txt10
    xlWS1.Cells(22, 4).Value = Forms!frmMainMenu!txtd11
    xlWS1.Cells(22, 6).Value = Forms!frmMainMenu!txt11
    xlWS1.Cells(24, 4).Value = Forms!frmMainMenu!txtd12
    xlWS1.Cells(24, 6).Value = Forms!frmMainMenu!txt12
    xlWS1.Cells(26, 4).Value = Forms!frmMainMenu!txtd13
    xlWS1.Cells(26, 6).Value = Forms!frmMainMenu!txt13
    xlWS1.Cells(28, 4).Value = Forms!frmMainMenu!txtd14
    xlWS1.Cells(28, 6).Value = Forms!frmMainMenu!txt14
    If Me.txtTotalDrops = "14" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    xlWS1.Cells(21, 4).Value = Forms!frmMainMenu!txtEnt10 & " >> " & Forms!frmMainMenu!txtExt10
    xlWS1.Cells(23, 4).Value = Forms!frmMainMenu!txtEnt11 & " >> " & Forms!frmMainMenu!txtExt11
    xlWS1.Cells(25, 4).Value = Forms!frmMainMenu!txtEnt12 & " >> " & Forms!frmMainMenu!txtExt12
    xlWS1.Cells(27, 4).Value = Forms!frmMainMenu!txtEnt13 & " >> " & Forms!frmMainMenu!txtExt13
    xlWS1.Cells(29, 4).Value = Forms!frmMainMenu!txtEnt14 & " >> " & Forms!frmMainMenu!txtExt14
    End If
    If Me.txtTotalDrops = "13" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    xlWS1.Cells(21, 4).Value = Forms!frmMainMenu!txtEnt10 & " >> " & Forms!frmMainMenu!txtExt10
    xlWS1.Cells(23, 4).Value = Forms!frmMainMenu!txtEnt11 & " >> " & Forms!frmMainMenu!txtExt11
    xlWS1.Cells(25, 4).Value = Forms!frmMainMenu!txtEnt12 & " >> " & Forms!frmMainMenu!txtExt12
    xlWS1.Cells(27, 4).Value = Forms!frmMainMenu!txtEnt13 & " >> " & Forms!frmMainMenu!txtExt13
    End If
    If Me.txtTotalDrops = "12" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    xlWS1.Cells(21, 4).Value = Forms!frmMainMenu!txtEnt10 & " >> " & Forms!frmMainMenu!txtExt10
    xlWS1.Cells(23, 4).Value = Forms!frmMainMenu!txtEnt11 & " >> " & Forms!frmMainMenu!txtExt11
    xlWS1.Cells(25, 4).Value = Forms!frmMainMenu!txtEnt12 & " >> " & Forms!frmMainMenu!txtExt12
    End If
    If Me.txtTotalDrops = "11" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    xlWS1.Cells(21, 4).Value = Forms!frmMainMenu!txtEnt10 & " >> " & Forms!frmMainMenu!txtExt10
    xlWS1.Cells(23, 4).Value = Forms!frmMainMenu!txtEnt11 & " >> " & Forms!frmMainMenu!txtExt11
    End If
    If Me.txtTotalDrops = "10" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    xlWS1.Cells(21, 4).Value = Forms!frmMainMenu!txtEnt10 & " >> " & Forms!frmMainMenu!txtExt10
    End If
    If Me.txtTotalDrops = "9" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    xlWS1.Cells(19, 4).Value = Forms!frmMainMenu!txtEnt9 & " >> " & Forms!frmMainMenu!txtExt9
    End If
    If Me.txtTotalDrops = "8" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    xlWS1.Cells(17, 4).Value = Forms!frmMainMenu!txtEnt8 & " >> " & Forms!frmMainMenu!txtExt8
    End If
    If Me.txtTotalDrops = "7" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    xlWS1.Cells(15, 4).Value = Forms!frmMainMenu!txtEnt7 & " >> " & Forms!frmMainMenu!txtExt7
    End If
    If Me.txtTotalDrops = "6" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    xlWS1.Cells(13, 4).Value = Forms!frmMainMenu!txtEnt6 & " >> " & Forms!frmMainMenu!txtExt6
    End If
    If Me.txtTotalDrops = "5" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    xlWS1.Cells(11, 4).Value = Forms!frmMainMenu!txtEnt5 & " >> " & Forms!frmMainMenu!txtExt5
    End If
    If Me.txtTotalDrops = "4" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    xlWS1.Cells(9, 4).Value = Forms!frmMainMenu!txtEnt4 & " >> " & Forms!frmMainMenu!txtExt4
    End If
    If Me.txtTotalDrops = "3" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    xlWS1.Cells(7, 4).Value = Forms!frmMainMenu!txtEnt3 & " >> " & Forms!frmMainMenu!txtExt3
    End If
    If Me.txtTotalDrops = "2" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    xlWS1.Cells(5, 4).Value = Forms!frmMainMenu!txtEnt2 & " >> " & Forms!frmMainMenu!txtExt2
    End If
    If Me.txtTotalDrops = "1" Then
    xlWS1.Cells(3, 4).Value = Forms!frmMainMenu!txtEnt1 & " >> " & Forms!frmMainMenu!txtExt1
    End If
    
xlWS1.Cells(3, 2).Value = UCase(Forms!frmMainMenu!txtJSDriver)
xlWS1.Cells(4, 2).Value = Format(Me.txtDelDate - 1, "ddd-dd-mmmm-yyyy", vbUseSystemDayOfWeek)
xlWS1.Cells(5, 2).Value = Format(Me.txtDelDate, "ddd-dd-mmmm-yyyy", vbUseSystemDayOfWeek)
xlWS1.Cells(12, 2).Value = "    YES     NO"


xlWS1.Cells(14, 2).Value = ""
xlWS1.Cells(1, 3).Value = "Del No:"
xlWS1.Cells(1, 4).Value = "DESTINATION DETAILS:"
xlWS1.Cells(23, 2).Value = "** MOTORWAY ONLY **"
xlWS1.Cells(24, 2).Value = "** NOT MOTORWAY **"
xlWS1.Cells(26, 2).Value = iJS
xlWS1.Cells(27, 2).Value = "        YES         YES"
xlWS1.Cells(28, 2).Value = "        YES         YES"
xlWS1.Cells(30, 1).Value = "Max Fuel Price:"
xlWS1.Cells(30, 2).Value = Format(dtDelDate, "mmm") & " " & Format(dtDelDate, "yyyy") & " " & strFuel
xlWS1.Cells(1, 5).Value = "DEL-COL"
xlWS1.Cells(1, 6).Value = "Del Items"


xlApp.Quit


ExitHandler:
  On Error Resume Next
  xlApp.Quit
  Set xlApp = Nothing
  Set xlWS1 = Nothing
    Last edited by DMT Dave; 07-13-2022 at 09:24 AM. Reason: Added jpg
    Reply With Quote Reply With Quote
  15. 07-13-2022, 09:42 AM #15
    Minty
    Minty is offline VIP
    Windows 10 Office 365
    Join Date
    Sep 2017
    Location
    UK - Wiltshire
    Posts
    3,157
    You need to save the wb before quitting, so at the end do this

    Code:
    xlWb.Save
xlApp.Quit
Set xlApp = Nothing
    Otherwise I suspect it leaves a open WB with pending saves that will stay in the ether even though you are setting xlApp to nothing.
    and yes I'm absolutely certain your code is not very efficient, but I'd need a bit of time to have a look.

    This line makes no sense to me given it's position?

    Code:
    On Error Resume Next
    DLookup Syntax and others http://access.mvps.org/access/general/gen0018.htm
    Please use the star below the post to say thanks if we have helped !
    ↓↓ It's down here ↓↓
    Reply With Quote Reply With Quote
Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.
« Previous Thread | Next Thread »

Similar Threads

  1. Button to create/open or copy/open an excel file
    By petrikos in forum Programming
    Replies: 23
    Last Post: 10-15-2021, 07:00 AM
  2. How to transpose data ; make one record to 9 records and copy to excel template starting from A3
    By stalk in forum Queries
    Replies: 5
    Last Post: 02-01-2020, 05:27 AM
  3. Rename word document during copy/paste
    By Homegrownandy in forum Programming
    Replies: 3
    Last Post: 07-17-2018, 08:15 AM
  4. How can I select a file (jpg) and copy (and rename) it .
    By vito1010 in forum Modules
    Replies: 6
    Last Post: 08-16-2014, 11:20 AM
  5. Copy & Rename a report
    By Juanna_v in forum Reports
    Replies: 25
    Last Post: 02-17-2011, 12:03 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

Forum Rules

Other Forums: Microsoft Office Forums