Update VBA within an existing Excel based timesheet system on Office 2016 for Mac
- or -
Post a project like this$$$
- Posted:
- Proposals: 2
- Remote
- #1014016
- Expired
Description
Experience Level: Expert
General information for the business: We are a Public Relations consultancy that uses timesheets to record and bill our clients
Kind of development: Customization of existing program
Description of every module: We have a timesheet document, based on each member of staff's desktop, which reads and accesses files stored on a single networked drive in the same office.
Currently we are using Excel within Office 2011 for Mac (referred to here as Excel 2011. We need to start using Office 2016 for Mac but changes to the way Excel 2016 works means the existing timesheet system will not work.
The heart of the problem is whereas Excel 2011 was able to read and access external files freely, Excel 2016 will not without user intervention i.e. file access permission dialogue boxes each and every time a file is accessed. Our system automatically reads and rewrites to a number of files on the networked drive in the background and cannot operate with this new process.
In 2015 Microsoft issued a number of new VBA commands which sought to address the issue but this still required the user to approve file access but only the first time a file is used, after the that the grant of access becomes automatic.
The sub-routine require the names of the files to be accessed to be stated in the code. This is fine for the majority of the files the timesheet system accesses, which have static names, but it does not work for the new data entries the system creates each time the user records a new timesheet entry. These file are created on the fly each time the user saves the timesheet.
Our task is to try, if possible, to integrate the new VBA commands into our existing system.
The new VBA command and background to this issue can be found on the following Microsoft support page:
https://dev.office.com/blogs/VBA-improvements-in-Office-2016
Scroll down to the heading: GrantAccessToMultipleFiles
Description of requirements/functionality: To recap on how our system works:
• On startup our Macs mount as external drive on out network: Untitled1.
• The Mac then opens the file Timesheet.xlsm on the desktop, which looks for Untitled1 and reads and loads data from a number of files with static names within the drive, or what it calls Home_Folder = "Untitled1:Timesheets:"
The files are:
- Home_Folder & "Default Lists\Task.txt"
- Home_Folder & "Default Lists\Names.txt"
- Home_Folder & "Default Lists\Clients.txt"
It also reads and loads the relevant txt file within the Data folder (Home_Folder & "Data", which corresponds with the date selected in the Date drop down in the UI.
The date drop down menu in the UI enable the user to access the last 14 days of data. When the user selects a new date the timesheet access the relevant txt file on the external drive i.e. "Andrew 10 Jan 16.txt" or "Andrew 11 Jan.txt". When making the first entry on a new day the timesheet file writes a new txt file and places it along side the other txt files on the external drive. It's these new files, created on the fly, that are the problem because they won't exist when the new VBA command is set up.
The same problem is apparent in the two Report files (Report.xlsm and Report Maker.xlsm). The new sub should resolve the problem in the Report file because this always reads Report Maker.xlsm and nothing else. However Report Maker.xlsm compiles literally hundreds of individual txt file - every file that is found in the Data folder - and I sometimes go in to the Data folder and manually create new txt files where time has been missed more than 14 days ago (the limit for making txt files in the timesheet UI). We run Report Maker to compile the txt files – it opens, runs and then automatically closes – and then run the Report file which reads the compiled data held in Report Maker. We found that one file couldn't both compile and also display the data without a 10-12 minute wait! On average the Data folder holds around 2,00 individual txt files, although I've deleted most from the attachment and left you with just a few.
My final issue is whether the new sub will store the access grants when a computer is shut down and then restarted, as happens at the end of each day and beginning of the next.
Specific technologies required: VBA & Office 2016 for Mac running on Mac OS El Capitan
OS requirements: Mac OS
Extra notes: If you feel you have the relevant experience and understand of VBA within Excel for Mac, then please get in touch and I will forward the relevant existing files so you can very simply set up an operational system and look through the issue in detail.
Kind of development: Customization of existing program
Description of every module: We have a timesheet document, based on each member of staff's desktop, which reads and accesses files stored on a single networked drive in the same office.
Currently we are using Excel within Office 2011 for Mac (referred to here as Excel 2011. We need to start using Office 2016 for Mac but changes to the way Excel 2016 works means the existing timesheet system will not work.
The heart of the problem is whereas Excel 2011 was able to read and access external files freely, Excel 2016 will not without user intervention i.e. file access permission dialogue boxes each and every time a file is accessed. Our system automatically reads and rewrites to a number of files on the networked drive in the background and cannot operate with this new process.
In 2015 Microsoft issued a number of new VBA commands which sought to address the issue but this still required the user to approve file access but only the first time a file is used, after the that the grant of access becomes automatic.
The sub-routine require the names of the files to be accessed to be stated in the code. This is fine for the majority of the files the timesheet system accesses, which have static names, but it does not work for the new data entries the system creates each time the user records a new timesheet entry. These file are created on the fly each time the user saves the timesheet.
Our task is to try, if possible, to integrate the new VBA commands into our existing system.
The new VBA command and background to this issue can be found on the following Microsoft support page:
https://dev.office.com/blogs/VBA-improvements-in-Office-2016
Scroll down to the heading: GrantAccessToMultipleFiles
Description of requirements/functionality: To recap on how our system works:
• On startup our Macs mount as external drive on out network: Untitled1.
• The Mac then opens the file Timesheet.xlsm on the desktop, which looks for Untitled1 and reads and loads data from a number of files with static names within the drive, or what it calls Home_Folder = "Untitled1:Timesheets:"
The files are:
- Home_Folder & "Default Lists\Task.txt"
- Home_Folder & "Default Lists\Names.txt"
- Home_Folder & "Default Lists\Clients.txt"
It also reads and loads the relevant txt file within the Data folder (Home_Folder & "Data", which corresponds with the date selected in the Date drop down in the UI.
The date drop down menu in the UI enable the user to access the last 14 days of data. When the user selects a new date the timesheet access the relevant txt file on the external drive i.e. "Andrew 10 Jan 16.txt" or "Andrew 11 Jan.txt". When making the first entry on a new day the timesheet file writes a new txt file and places it along side the other txt files on the external drive. It's these new files, created on the fly, that are the problem because they won't exist when the new VBA command is set up.
The same problem is apparent in the two Report files (Report.xlsm and Report Maker.xlsm). The new sub should resolve the problem in the Report file because this always reads Report Maker.xlsm and nothing else. However Report Maker.xlsm compiles literally hundreds of individual txt file - every file that is found in the Data folder - and I sometimes go in to the Data folder and manually create new txt files where time has been missed more than 14 days ago (the limit for making txt files in the timesheet UI). We run Report Maker to compile the txt files – it opens, runs and then automatically closes – and then run the Report file which reads the compiled data held in Report Maker. We found that one file couldn't both compile and also display the data without a 10-12 minute wait! On average the Data folder holds around 2,00 individual txt files, although I've deleted most from the attachment and left you with just a few.
My final issue is whether the new sub will store the access grants when a computer is shut down and then restarted, as happens at the end of each day and beginning of the next.
Specific technologies required: VBA & Office 2016 for Mac running on Mac OS El Capitan
OS requirements: Mac OS
Extra notes: If you feel you have the relevant experience and understand of VBA within Excel for Mac, then please get in touch and I will forward the relevant existing files so you can very simply set up an operational system and look through the issue in detail.
Andrew K.
0% (0)Projects Completed
-
Freelancers worked with
-
Projects awarded
0%
Last project
19 Apr 2024
United Kingdom
New Proposal
Login to your account and send a proposal now to get this project.
Log inClarification Board Ask a Question
-
There are no clarification messages.
We collect cookies to enable the proper functioning and security of our website, and to enhance your experience. By clicking on 'Accept All Cookies', you consent to the use of these cookies. You can change your 'Cookies Settings' at any time. For more information, please read ourCookie Policy
Cookie Settings
Accept All Cookies