Purchase Order macros

[AndyB86]

Hi all,

I've been tasked with making a new PO system for our business on Excel (the 'old' one was on Microsoft Access, but that's not compatible with new software we've had recently). I've found a few tutorials online and have made up some macros from those, but there are parts of what I've been tasked to do that I can't find any tutorials on, so I'm not sure if it's even possible! I'm not looking for someone to write a whole PO system for me, I'm not that cheeky, just a point in the right direction of if a) it's possible and b) where a tutorial might be. We perhaps only require 10-20 POs a month, so it's not worth buying a pre-made system if it's possible to make one myself on Excel, but I'm very new to macros, VBA etc so I'm having to teach myself from forums and YouTube! What my boss wants is:

• Auto-generate new PO numbers and auto-clear data from previous PO (I've found a macro to do this which works fine)
• Maintain all POs within the same workbook so that any new POs are created in a new worksheet (can't find any way to do this so far, only to generate the new PO in the same worksheet, thus erasing the old one)
• Save a PDF copy of the PO from within each worksheet (I found a macro to do this, it worked on my computer, but then corrupted when I attempted to demonstrate it on someone else's computer, so I ended up having to delete that PO file and start again)
• Auto-copy data from a previous PO for a repeat order, but with the new PO number and today's date (can't find any way to do this so far) - this one's not as important as the above, we can make do if it's not possible or extremely complicated

It's also become apparent that macros don't seem to work if you access the file via SharePoint (you have to use the 'edit in desktop app' option). Is there any way around this as I'm fairly sure some of my co-workers won't understand this and will complain it 'doesn't work' if they can't use it direct from SharePoint.

 

[Micron]

the 'old' one was on Microsoft Access, but that's not compatible with new software we've had recently)

I find that intriguing and if it were me I'd try my best to make that happen. Access can easily achieve your bulleted points and it's really the right tool for the job for so many reasons. But I guess with enough code and sheets set up as required you may be able to get there but I have no idea how I'd do this in Excel so can't help you there. If the boss is open to figuring out why Access isn't compatible and how to remedy that, maybe I can provide some guidance. Traveling today so may not get back here for a bit.

 

[AndyB86]

I find that intriguing and if it were me I'd try my best to make that happen. Access can easily achieve your bulleted points and it's really the right tool for the job for so many reasons. But I guess with enough code and sheets set up as required you may be able to get there but I have no idea how I'd do this in Excel so can't help you there. If the boss is open to figuring out why Access isn't compatible and how to remedy that, maybe I can provide some guidance. Traveling today so may not get back here for a bit.

Hi, thanks for your reply.

We changed from an internal, on site server that was backed up at a remote location ("Managed Access" package through a third party IT firm) to everything being cloud-based through SharePoint. Ever since we changed Microsoft Access won't open - not on SharePoint or on any individual computer as a desktop programme. I don't have a lot to do with the IT side of things, so I'm not sure of any of the technical specs of the old server or the remote back-up or anything like that, I just know Microsoft Access won't open on any device on site any more. (We haven't changed any of the Office software on any of the individual computers, we only changed from a physical server to SharePoint.)

If we can get Microsoft Access to work again then I agree that would solve the issue. The reason my boss wants an Excel that can do the bulleted points is because that's what the 'old' system did (until it stopped opening!), so getting the 'old' system back would be a big plus.

 

[AndyB86]

In follow up to the above, when I say Microsoft Access won't open, it's coming up with this message:

If you click 'Open' it just comes up with a blank white screen and nothing else happens. If you click 'Download' it will open the last saved PO, but completely uneditable (the 'Previous', 'Next' etc buttons at the bottom don't respond, can't enter any text etc). It also has this error message at the top of the page at that point which never used to happen

 

[Micron]

Access is a stand alone desktop app (as you may already know) and if the app (not a database file) won't open on a desktop pc then IT must be blocking that somehow. Your pic suggests to me that you're trying to open a db that was sent in an email. As for the pink message, it means the folder that the file is located in is not in a Trusted Location (google that term if you need to). Put the file in a trusted location and see what happens when you try to open it. Of course, you must have Access full install or at least the runtime version to open it. Runtime will not allow you to make design changes.

As for Sharepoint, either Access or Excel can pull/push data between them and SP if that should become an need. Gotta hit the road now...

 

[AndyB86]

Access is a stand alone desktop app (as you may already know) and if the app (not a database file) won't open on a desktop pc then IT must be blocking that somehow. Your pic suggests to me that you're trying to open a db that was sent in an email. As for the pink message, it means the folder that the file is located in is not in a Trusted Location (google that term if you need to). Put the file in a trusted location and see what happens when you try to open it. Of course, you must have Access full install or at least the runtime version to open it. Runtime will not allow you to make design changes.

As for Sharepoint, either Access or Excel can pull/push data between them and SP if that should become an need. Gotta hit the road now...

Thanks for the explanation. Just a couple of points I'll pick up from your response in case this makes any difference at all:

• The above pic is the message we get when we try and open the file direct from it's SharePoint location (not an emailed file). As our physical server was discontinued when everything was uploaded to SharePoint, the SharePoint location is now the only location where the db file exists. It is the only file on the whole of our SharePoint that gets this message (it is also the only Microsoft Access file on our SharePoint). We asked our third party IT support firm (who handled our SharePoint migration and manage all our devices) why this one file wouldn't open, and they said it was because Microsoft Access is not compatible now we've changed from a physical server to SharePoint (hence my original post), but if you're saying it is compatible with SharePoint then I don't know what's happened there.
• The 'pink message' is the message we get if we download the file from SharePoint to an individual computer. That would place it in the downloads folder of that computer/user, which is a trusted location. This is the only file which gets that 'pink message' from the downloads folder of anyone's computer, and the only file that gets that 'pink message' when downloaded from our SharePoint as both the downloads folders of each user and the SharePoint as a whole should be trusted locations (they are for every file except this one!).

If it's something our IT support guys have either done incorrectly or blocked in error etc then we will have to speak to them again, but when we raised it before they were adamant it is nothing to do with them, and nothing they can resolve, it's a compatibility issue between Microsoft Access and SharePoint.