Toad World® Forums

Excel Macros on TIC , file created during run time is retaining data from previous macro executions.


#1

Hi, I have an automation script published on TIC, that uses an export activity which runs an excel macro after the sql export.

macro does a calculation which is , multiply value in cell D4 with 100. This calculation happens in the file created during run time but every time this script runs it retains multiplied value from the previous runs, which should not happen as multiplication happened during the run time and the orginal file value should not be multiplied and saved.

Capture.jpeg

This happens if I run script multiple times on the same date:

so for example let’s assume initial value in cell D4 is 1, after first run on 1/4/2017 it will become 100, after second run on 1/4/2017 it will be 10000, after third run on 1/4/2017 it will be 1000000

however on 1/5/2017 it will again be 100 after first run ,10000 after second and so on.

My understanding is all these files are created during run time and any calculation during the execution of macro,should not affect the original xlsm file


#2

Try adding a macro that clears the data area prior to exporting the data. See this video http://www.toadworld.com/products/toad-data-point/m/media-library/1143


#3

Do you save the excel file with a date added? My guess is that any second, third etc update on a given day is using the file that has the current date. Strange.


#4

Hi Peter,

Yes, file created during run time is suffixed with date, all the files on the given day have same name e.g. data_2018_01_15.xlsm.

Strangely , all the second , third and so on , updates on a given date are using the file which was created during the first update on that particular day as they all have same name.

Ideally, it should not happen, I have other automation scripts this does not happen there.

To resolve my issue, I have to use date with time as a suffix for the file name e.g. data-2018_01_15 08 23.xlsm

I seems to me that it is using the same temp file that it creates during the first run on a given date for all the subsequent run on that particular date because of same file name.


#5

Hi Peter,

Yes, the file name contains date.

For some reasons it is using the same file, that it creates during the first run on a particular date for all subsequent runs on that particular date.

The issue gets fixed once I use datetime suffix instead of just date suffix as the file name changes during each run

But ideally, it should not be using the same file for subsequent runs


#6

In our last release we went through our export to file matrix. We made some changes and documented the matrix below. If you feel we are not following the matrix below or that we missed a scenario can you post your automation script and output files so we can review? If you do not want this shown on the forum then you can make a support case and we will pick up this topic.

More About Excel Export Options

Automation scripts are a powerful and useful tool in Toad. For many users, the end result of their script is an Excel report. Toad allows you to specify a number of options for the exported Excel report. The outcome is determined by the combination of settings specified in the Export options dialog.

This topic focuses on how to use some of the settings in the Export options dialog and the Export Wizard. To review the Export options dialog, see Specify Excel Export Options. To review the Export Wizard, see Export Data with the Export Wizard.

Using the Overwrite and Date/Time Suffix Options

The following table provides some common use cases involving the Export Wizard or the Select to File activity. In particular, this table describes the expected outcome when different Overwrite and Date/Time Suffix settings are used. In these example scenarios, the output is a single file.

Scenario 1. Export Wizard—One object or one query

Export Options
Output (single file)
Overwrite
Suffix
Toad
File Name
Comments
1a
Y
Y
Creates a new blank file (with suffix) and exports to it
original name + suffix
If identical suffixed file name exists, it is deleted.
1b
Y
N
Creates a new blank file and exports to it
original name
Deletes the original file.
1c
N
Y
Copies original file, adds a suffix, and exports to it
original name + suffix
See the Append worksheet examples below.

1d
N
N
Exports to original file
original name
See the Append worksheet examples below.

Scenario 2. Export Wizard—Two queries (in one SQL script) or Select to File—Two queries

Export Options
Output (single file)
Overwrite
Suffix
Toad
File Name
Comments
2a
Y
Y
Creates a new blank file (with suffix), exports 1st result set, then exports 2nd result set to same file
original name + suffix
If identical suffixed file name exists, it is deleted.
2b
Y
N
Creates a new blank file and exports to it
original name
Deletes the original file.
2c
N
Y
Copies original file, adds a suffix, and exports to it
original name + suffix
To ensure that each result set is exported to a separate worksheet, select Append worksheet with no custom name. Otherwise, both result sets are exported to the same selected worksheet.

See the Append worksheet examples below.

2d
N
N
Exports to original file
original name
To ensure that each result set is exported to a separate worksheet, select Append worksheet with no custom name. Otherwise, both result sets are exported to the same selected worksheet.

See the Append worksheet examples below.

Scenario 3. Export Wizard—Two objects or two queries (as individual Query objects)

Export Options
Output (single file)
Overwrite
Suffix
Toad
File Name
Comments
3a
Y
Y
Creates a new blank file (with suffix) and exports to it.
original name + suffix
If identical suffixed file name exists, it is deleted.

Each object has its own options.

3b
Y
N
Creates a new blank file and exports to it.
original name
Deletes the original file.

Each object has its own options.

3c
N
Y
Copies original file, adds a suffix, and exports to it, one object at a time.
original name + suffix
See the Append worksheet examples below.

Each object has its own options.

3d
N
N
Exports to the original file, one object at a time.
original name
See the Append worksheet examples below.

Each object has its own options.

Notes:

  • When the Date/Time Suffix option is selected, the previously-created suffixed files are retained.
  • If you want to export to two or more named worksheets, use the Export Wizard with scenario 3. Do not use multiple queries in one SQL script (scenario 2).
  • Append worksheet is the default setting.

Append Worksheet with no Overwrite

When you are appending a worksheet and not overwriting the file, each Suffix setting produces a different output. Review the output for each Suffix setting:

  • Suffix—When a file name suffix is added, Toad creates a copy of the original file and then exports to it.
  • No Suffix—If no suffix is added, with each execution Toad adds a new worksheet to the working file and then exports toit.
    • Default—If the default Worksheet name is used (Sheet#), Toad adds Sheet 2, Sheet 3, etc.
    • Timestamp—If you select Append timestamp to named worksheet, Toad adds a timestamped worksheet.
      Review the following examples of output files for each Automation scenario.

Scenario
Append worksheet
Over-write
Suffix
Original File Contents
After 1st execution
After 2nd execution
After 3rd execution
1 Query

(1c)

Y
N
Y
Sheet 1
Sheet 1, Sheet 2
Sheet 1, Sheet 2
Sheet 1, Sheet 2
2 Queries

(2c, 3c)

Sheet 1, Sheet 2
Sheet 1, Sheet 2, Sheet 3, Sheet 4
Sheet 1, Sheet 2, Sheet 3, Sheet 4
Sheet 1, Sheet 2, Sheet 3, Sheet 4
1 Query

(1d)

N
Sheet 1
Sheet 1, Sheet 2
Sheet 1, Sheet 2, Sheet 3
Sheet 1, Sheet 2, Sheet 3, Sheet 4
2 Queries

(2d*, 3d*)

Sheet 1, Sheet 2
Sheet 1, Sheet 2, Sheet 3, Sheet 4
Sheet 1, Sheet 2 Sheet 3, Sheet 4, Sheet 5, Sheet 6
Sheet 1, Sheet 2 Sheet 3, Sheet 4, Sheet 5, Sheet 6, Sheet 7, Sheet 8
* For best results, consider using the Append timestamp to named worksheet option to append a worksheet with each script execution.


#7

https://drive.google.com/open?id=1DT82MHcdRbVKtdWqUrKGzotd-DntaC9h

All the related files, like the export tempelate, automation script, and excel macro file are at the above path.


#8

Vaibhav,

When export to an Excel file with date suffix, we copy the original file and save it as a new file with suffix, and export data to this new file. However, on the subsequent execution, the suffixed file already exist, we simply use the existing file, that’s why you see the number getting bigger and bigger. This is very useful when you have multiple queries in a single export template, we will have all results sets into the single file, otherwise, only last result set is kept. To workaround it, you either use datetime suffix or add a separate activity to delete the suffixed file so you are sure every time you are using the original file.

Please let me know if you have questions.

Thanks!


#9

That would make sense during a single execution of a script, but not in subsequent running of the script. Each new running of a script should use the original excel template. That is the only logical way.


#10

Thanks all for helping me out !