Toad DataPoint 3.4 Task Automation

Toad Data Point
1

Hi
I am using Toad Datapoint 3.4 ( a new user)

I have a query which produces data with headings in columns A to L.
I have been unsuccessful in automating the task to do what I want, which is:
1 Run the query
2 Save the data A1 : L4000 to a tab named Data in an existing work book, and only overwriting the columns A:L as there are formulae in columns M:S
There are also 5 other tabs with pivots.
So, I also want to
3 Refresh all pivots.
4 Save as filename with datestamp.
I was trying to automate using select to file, but that removes my other tabs and formulae.

I suspect I should not be using select to file, but I am not sure what I should use.

Thankyou for any help given.

CK.

2

Make sure the overwrite checkbox is unchecked. You need to use the advanced button on the select to file so you can export to just the 1 tab. Now here is where you may have a problem. You need to clear the worksheet unless your data is always the same number of rows (there is a checkbox for that on the advanced tab). Clearing the worksheet will remove your formulas. So if you need to clear the worksheet you need to move your formulas to another worksheet with the formulas still pointing to the Data tab. You can set your spreadsheet to auto refresh on opening but I have had limited success with that method. I prefer using the automation to open Excel with an .xlsm macro file in the argument line. The macro file can open your spreadsheet with today’s date appended to the file name and then refresh the pivots as well as dynamically set the pivot range if you like and even build your formulas if you want them on the same sheet. You will need to name the macro Auto_Open() so it launches on opening and include a Application.Quit statement at the end so Excel closes and returns control to the automation job.

3

Hi Greg

I didn’t read your answer thoroughly enough. I see that you have told me how to date stamp it. Thank you.

C

Caroline King | Business Analyst | Downer

T 092510228

, Auckland, 2104, New Zealand

Private Bag 93325, Otahuhu, New Zealand

From: GregDavis11009 [mailto:bounce-GregDavis11009@toadworld.com]
Sent: Tuesday, 18 March 2014 03:47
To: toaddatapoint@toadworld.com
Subject: RE: [Toad Data Point - Discussion Forum] Toad DataPoint 3.4 Task Automation

RE: Toad DataPoint 3.4 Task Automation

Reply by GregDavis11009

Make sure the overwrite checkbox is unchecked. You need to use the advanced button on the select to file so you can export to just the 1 tab. Now here is where you may have a problem. You need to clear the worksheet unless your data is always the same number of rows (there is a checkbox for that on the advanced tab). Clearing the worksheet will remove your formulas. So if you need to clear the worksheet you need to move your formulas to another worksheet with the formulas still pointing to the Data tab. You can set your spreadsheet to auto refresh on opening but I have had limited success with that method. I prefer using the automation to open Excel with an .xlsm macro file in the argument line. The macro file can open your spreadsheet with today’s date appended to the file name and then refresh the pivots as well as dynamically set the pivot range if you like and even build your formulas if you want them on the same sheet. You will need to name the macro Auto_Open() so it launches on opening and include a Application.Quit statement at the end so Excel closes and returns control to the automation job.

To reply, please reply-all to this email.

Stop receiving emails on this subject.
Or Unsubscribe from Toad Data Point - General notifications altogether.
Toad Data Point - Discussion Forum

Flag this post as spam/abuse.

Note:

This message is for the named person’s use only. It may contain confidential, proprietary or legally privileged information. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. No confidentiality or privilege is waived or lost by any such mis-transmission or error.

Downer reserves the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

Downer includes Downer New Zealand Limited and Downer EDI Engineering Limited and their related and associated companies.

This email has been filtered by SMX. For more information visit smxemail.com

4

To add the date stamp to the file I would use the copy file activity in the job after the select to file. Copy from and to the same location and name and check the box for append timestamp. Or if you are using the macro save the file as “name” + time stamp + “.xlsx” there.