Impromptu Macro - HELP!!!!

[fishax]

Hi All,
I want to do something really simple. I want to Open a Report in Impromptu, then RUN A MACRO that will save the report as an excel file to a SPECIFIED PATH. The excel file should have the SAME NAME as the original report except with extension .xls. I am able to save the file
to the original path that I opened report from, but to a specified path...haven't been successful. For example below, I want to save the Report to the C:\ location with the same filename as the report e.g. c:\report.xls

Sub Main()
'declare variables
Dim objImpApp As Object
Dim objImpRep As Object
Dim strASCIIFileName As String
Set objImpApp = CreateObject("Impromptu.Application&quot
'retrieve the active report
Set objImpRep = objImpApp.ActiveDocument
'get the report name and path
strASCIIFileName = Left$(objImpRep.FullName,
Len(objImpRep.FullName) - 4)
'export as an ASCII file

objImpRep.ExportASCII ("c:\" & strASCIIFileName & ".xls" ) --> {*****HELP!!!THIS IS WHERE THE PROBLEM LIES********)

'release variables
Set objImpRep = Nothing
Set objImpApp = Nothing
End Sub

Can anyone Help Point me in the right direction. Am using version 5 of impromptu

 

[fishax]

Hello,
Thanks for the help so far. Did find a way to embed the parameters in the Macro itself.

However, it seems like I need to lock down the file, since there is a danger of User's editing. Can't do so in its original location since the Macro creates an empty file, then adds the data to it...so WRITE ONCE permissions in OS don't work. Is it possible to create the file in ONE LOCATION, then MOVING IT into Another Location (i.e. it should no longer exist in LOCATION ONE). Please let me know if thru the macro, this is possible.

 

[fishax]

Hi,
Don't worry guys....have figured it out.

Used FileCopy and Kill to accomplish this. I am creating the file in a temp location, then copying it over. Then use the Kill to delete it from the temp location.

If there is another way, let me know. I am enclosing the copy of the code.

------------------------------------------------------
Sub Main()
'declare variables
Dim objImpApp As Object
Dim objImpRep As Object
Dim strASCIIFileName As String


Set objImpApp = CreateObject("Impromptu.Application&quot

'retrieve the active report
Set objImpRep = objImpApp.ActiveDocument

'get the report name and path
strASCIIFileName = Left$(objImpRep.Name, Len(objImpRep.Name) - 4)

'export as an ASCII file
objImpRep.ExportASCII "C:\tmp\" & strASCIIFileName & ".csv"

'copy file to another folder that is locked down FileCopy "C:\tmp\" & strASCIIFileName & ".csv", "C:\data\" & strASCIIFileName & ".csv"

'delete csv files in tmp folder
kill "c:\tmp\*.csv"

'release variables
Set objImpRep = Nothing
Set objImpApp = Nothing

End Sub

---------------------------------------------------------

 

[fishax]

Hi All,
I would like to enhance the above code a wee bit. For the csv file created, I would like to Capture/Append to a TEXT FILE (in a certain path), the Path/FileName and its Date/Time Stamp. Is there a way to do this?

Also, one slight problem is that I need to somehow add error trapping, so that if the csv file already exists in the Location it is to be copied to, then instead of erroring out (which is what is currently happening), a message should appear "File Already Exists"

Any examples or tips from the Professionals here will definately help.

 

[griffindm]

Use the Dir function to see if the file already exists. If it does, then employee a Msgbox for the warning, such as:

ChkFile = Dir (filedest)
If ChkFile <> &quot;&quot; then
Msgbox &quot;Destination Files Exists!&quot;,0,&quot;Status
End if

A better solution might be to move the file to a new name that incorporates a date-time stamp in the name, thus insuring it will not collide with a currently existing file.

HTH,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[fishax]

Hi Dave,
Thanks for the ChkFile. It worked well.

However, I am struggling with something which seems pretty simple.

I want to Copy a File
C:\Temp\temp001.txt

to

C:\Temp\Text\temp001TODAYSDATE.txt

I am not able to append TODAYS DATE (system date/time) to the filename. Am using FileCopy....no luck.

 

[griffindm]

Fishax,

See my help on getting the date into a variable for the macro in thread401-292497.

Hope this helps,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com

 

[fishax]

Hi Dave,
Thanks...I actually had to move to someone's VB machine to figure out why the Macro was crashing. Since VB has a debugger, it was easy to see that the slashes were the cause of all the headaches.

The &quot;format&quot; was timely as I was looking for an example of this. It worked well.

Thanks again.

 

[griffindm]

If you find any posts in TT to be helpful, please click on the link in the post to mark in as a helpful/expert post. This helps in two ways. It becomes easier for other users to search for posts on a topic that a user found truly useful, and it gives a small degree of recognition to the user that posted the helpful response.

Regards,

Dave Griffin
The Decision Support Group
Reporting Consulting with Cognos BI Tools
&quot;Magic with Data&quot;

http://www.decisionsupportgroup.com