• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Updating cell data without opening file

Core4

New Member
Hello, we use an excel document as an invoice for our clients. Once it's created specifically to their info it only needs to be opened each month to change the "month" in 1 cell. I was hoping there was a way to apply that change to the 300 + invoices (workbooks) with out having to open each one. Appreciate any help I can get.
thanks,
 
Hi, yes once each file is opened and the month is updated the file is then e-mailed to each client.
thanks
 
It really depends.

1. Formula to update dates dynamically. Open & then Save. This can be done via VBA to automate as well. Using VBA here would be probably the easiest.

2. Use ADODB code to update value. This approach would not require workbook to be opened, but bit more complex.

3. Using OpenXML standard, and VBA update the underlying xml file to update value. While this is possible, it's way too complex for what it's worth.

4. If these files are stored in OneDrive for business and you have Office 365 subscription. Use MS PowerAutomate/Flow to update cell value on schedule. No need to open file.

It's bit hard to help you without all the details and sample workbook.
 
Thank you for the suggestions. I've attached a sample file of the invoice we use. The 2 highlighted cells are the one's that need to be updated prior to sending. What every method to do this you feel would be the easiest is greatly appreciated. Thanks!
 

Attachments

  • Sample Invoice.xlsx
    20.6 KB · Views: 6
But what version of Excel do you use? Office 365? If Office 365 I'd recommend 4. If not 1 or 2 would work.

And looks like you only need H3 updated.
 
Sorry, we use Microsoft Office 365. Yes, currently when the date cell is updated it's automatically changing the other file. I didn't know with what we are trying to do if both cells should have something done or just the one? Thank you again for your help.
 
Ok, then definitely option 4. Now, in order for MS PowerAutomate to update cell value, it requires Excel Table.

Would that be a problem?

If it is, then probably easier to do it in formula approach. Provided that update / sending of files occur at regular interval.

I.E. Jan invoice is sent out on Feb, Feb on Mar, etc.
 
Hi, honestly I'm not sur if Excel Table would be a problem. Can you tell me why it might? Thanks
 
I'll need to add it to separate section of workbook. Adding hidden sheet.

I don't know how you generate each file. So that may require modification to your current process. Not sure if that would be an issue.
 
We use that sample I attached and just add in client specific information in the beginning and then save it in their folder. From that point on it's only opened each month to update the month and then on any other occasion if there was an update to the information placed there in the beginning.
 
Ok then hidden sheet shouldn't be issue. I'll be in meeting next 2 hours. I'll have something for you tomorrow.
 
Core4
Did I understand that You will fill that 'Sample File's sheet manually after update 'one yellow' cell?

Could You have one sheet which has those 300 + invoices data IN ONE FILE?
You update there needed datas.
After that You manually update that 'one yellow' cell and ... press [ EMAIL ] and
Clients would get 'Blank Invoice'-sheet with Client's data in own workbook.
... why needs 300+ workbooks?
Okay ... at least one workbook needs - of course.
 
We keep the clients invoice in their folder with other specific files. We can't have 1 document that includes all client invoices.
Thanks
 
Core4
You could get copy of sent workbook to the clients folder too ...
'One press' ... 300+ client workbooks ...
and of course, PDF copies too.
 
Core4
As a new member, You should reread Forum Rules
 
We keep the clients invoice in their folder with other specific files.
Wait... so all invoices are not in the same folder and you have other Excel files in same folder?

That's going to be an issue. Since you'll need to identify which file to update (i.e. all 300+ invoices). Is there specific naming convention where I can perform pattern match?
 
Hello, each invoice is located in a folder specific to that client. There are no other excel files in that folder though.
 
Back
Top