Page 4 of 4 FirstFirst 1234
Results 46 to 48 of 48
  1. #46
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Steve,



    Thanks for your reply. I think you've accurately described the process, apart from a couple of minor points, which I'll explain next. My payment process works fine, so I'm not looking for help with this part. My next task is uploading the payment file to Access, but I'll talk about that separately.

    1) I open the blank payment sheet. It already exists in the folder on my computer with the same name as the job/invoice number. I have a VBA code to create new blank payment files with their own folder inserted where I want them to be on my computer.
    2) Correct. The manager's spreadsheet can be in any format he decides, and it varies, but it always has all the information I need to create my version of the payment file.
    3) Yes, it's a vlookup formula, referencing the MusiciansDetails spreadsheet, once I've entered the player codes (or got my VBA code to enter the players codes for me)
    4) Yes, I manually enter the details as you described. I can use my "Enter Payment Fields" VBA to speed up this process
    5) If I use my "Enter Payment Fields" VBA, any extra payment columns (up until Y) get automatically hidden. I've left enough space to enter up to 8 payment descriptions, but in practice I doubt I'll ever need 8. So the one I sent you before, columns V-Y will have been surplus columns.
    6) Correct. Then I use my "Send Emails" VBA to email all the players. It automatically attaches their PDF files to their emails.
    7) I just click on save. It already existed with the correct job number and name before I filled in any data.
    8) No, I don't clear the payment sheet. I keep it as a record of the job. I also print part of it off and put it in the folder. I have a "Create New Folder" VBA to create new blank payment sheets, as described above.

    I'll try to create and upload some files for you, as requested. However, I'm not looking for any help with this process, it's working fine for me. That's not to say I wouldn't welcome any suggestions! I'm the only person who has access to my payment system, and there is only one manager.

    As I mentioned before, I'm planning to create a VBA code to automatically upload my payment data to Access. I have an idea how to achieve this now. I'll give it a go, and if I get stuck, I can come back here again, if that's ok?!

    Many thanks for your help.

  2. #47
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I was asking because I was going to try and convert the Excel process to an Access dB. But if the manager can change the layout of the spreadsheet that he/she used to report the Job/musicians, it gets more difficult.
    I haven't done a lot of Access automation to control Excel for the past few years and I haven't had to write code in Excel for about 20 years. But after 3.5 hrs (more or less) I finally have a sub in Excel that uses ADO to write the Excel data to an Access dB.



    However I think in your first post, you asked how to write data from Excel to Access so you could search for job data easier.

    When you open the IDE in Excel, you will see "Module1". Open the Module and change line 18
    Code:
        'EDIT the path to your path where the Access dB is located         *******************************************
        dBPath = "D:\Documents\Orchestra\Musician Payments\FY ending 2021\Access_PaymentFileTEST.accdb;"
    to the path to the Access dB. Also change the name of the Access dB if you change the dB name.

    I added a button on the worksheet (next to the Run Discrepancy Checker button).

    Check the References - the ones I have checked are:
    Microsoft Access xx.0 Object Library
    Microsoft DAO 3.6 Object Library
    Microsoft ActiveX Data Objects 6.1 Library

    plus the Libraries you need -
    Outlook,
    Forms 2.0,
    Microsoft Office xx.0 Object Library .



    Also, I did make a few changes to the code in the "ThisWorkbook" module.



    Anyway, if you have questions/problems, post back.
    This has been interesting. Make me think about Excel programming. Took a while for it to start coming back - but I learned a lot.


    In the Access table, I did not create fields for all of the columns in Excel. You should use a query to do the calculations like "Total net", "VAT" and "Total" (Columns Z, AA and AB)
    Attached Files Attached Files

  3. #48
    neilsolaris is offline Competent Performer
    Windows 10 Access 2007
    Join Date
    May 2020
    Posts
    116
    Hi Steve,

    Thanks a lot! You've been incredibly helpful. I've had a quick look at what you've done and it looks great. I'll try it out in a bit, and let you know how I get on.

    By the way, I incorporated your earlier advice, and I had already changed the "Hide Tabs" code. I'll write your other changes into the official version next.

    Thanks again for all your hard work.

Page 4 of 4 FirstFirst 1234
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 02-10-2020, 07:28 AM
  2. Replies: 21
    Last Post: 01-21-2014, 05:04 PM
  3. Replies: 1
    Last Post: 01-11-2014, 12:39 PM
  4. Replies: 1
    Last Post: 03-30-2012, 11:57 PM
  5. Active X form control
    By amitsingha4u in forum Access
    Replies: 2
    Last Post: 05-18-2010, 12:21 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums