Skip to main content

Export to Excel Quickly?

Hello Everyone,
I use IDEA frequantly and our end product always has to be in excel file. Is there any quick script I could put together which exports the currently active database into an excel spreadsheet on my computer without any dialog boxes? I would like the xlsx file to just retain the name of the active database open (.imb file). I recorded a scripted, but honestly, am not very good with scripting. 
Could someone help me to modify it? I feel like 85% of the code is already there.
<code>
Sub Main
Call ExportDatabaseXLSX() 'file1.IMD
End Sub
 
' File - Export Database: XLSX
Function ExportDatabaseXLSX
Set db = Client.OpenDatabase()
Set task = db.ExportDatabase
task.IncludeAllFields
eqn = ""
task.PerformTask "C:\Data\Desktop\file1.XLSX", "Database", "XLSX", 1, db.Count, eqn
Set db = Nothing
Set task = Nothing
End Function
</code>
 

Brian Element Mon, 01/13/2014 - 08:10

Hello David and welcome to the site.

Try this out and see if it fits your needs.  There is a function Client.CurrentDatabase that gets the current database.  I then extracted the name of the database and had to remove the IMD and replace it with the XLSX extenstion.  When you run it, it should automatically save an Excel spreadsheet in your project folder.

Sub Main
	Call ExportDatabaseXLSX()	'1 Tender=MasterCard.IMD
End Sub


' File - Export Database: XLSX
Function ExportDatabaseXLSX
	Set db = Client.CurrentDatabase
	Set task = db.ExportDatabase
	dbname = Client.CurrentDatabase.Name
	dbname = Mid(dbname, 1, Len(dbname) - 3) & "XLSX" 'remove the imd from the name and add the excel extention
	task.IncludeAllFields
	eqn = ""
	task.PerformTask dbname , "Database", "XLSX", 1, db.Count, eqn
	Set db = Nothing
	Set task = Nothing
End Function

 

davidm Wed, 01/15/2014 - 16:28

Ahh Brian, thank you! It worked beautifully. Is there any modifications to save it to the desktop?  I have lots of working folders depending on which project I am on and it's easy to grab them from my desktop. 

Brian Element Thu, 01/16/2014 - 07:21

David, if you are using version 9 I would suggest you attach it to a ribbon for easy access.  I am at work now and don't have access to V9 so I will give more detailed instructions when I get home tonight.   Glad it worked.

Brian Element Thu, 01/16/2014 - 17:26

Hi David, like I said before you should attach this to the ribbon so that you have easy access to it.

I would first copy it to your local libray and then attach it to your ribbon.  Here are the steps if they don't make sense let me know and I can probably post a vidoe of how to do it.

  1. Go to your Library (if you are not sure where it is it would be next to the File Explorer tab on the left hand side)
  2. Under Current Project Library (where your script is stored) open the macros folder and right click on the script and select "copy to local library"
  3. Now it is part of the local library we will attach it the ribbon.
  4. Select the Macros ribbon and then select Bind to Ribbon
  5. In the Choose command select the New button at the bottom.
  6. Under filename browse to C:\Users\User\Documents\My IDEA Documents\Local Library
  7. Select the Macros.ILB folder
  8. Select your script
  9. Select a symbol from the list
  10. On the right hand side under "Customize the Ribbon" click on Macros and then click on the New Group button at the bottom.
  11. Select the Rename button and give it a name, this will create a group so you might want to call it "IDEAScripts" or something.  Click ok
  12. Now select your script from the left hand box and select the Add button.  The script will now be found under the IDEAScripts group so you can use it like any other item.

The reason I moved the script to the Local Library is if you ever delete or move the project the script will still be available.

Let me know if this makes sense.

 

seif2468 Tue, 09/19/2017 - 08:31

Iam using IDEA 3 
error on line: 12 - object doesn't support this property or method

Brian Element Thu, 09/21/2017 - 12:45

In reply to by seif2468

Hi seif2468, IDEA 3 doens't exist.  Can you post which version of IDEA you are using.  Also this error, did you copy the above script into your editor and try running it?