Thread: Export to excel

I have data in ms-access database.I want to export it to Excel on click of a button which is in front end developed in core java.
I am working on frames NOT applet.

I developed a macro in access to export data to excel.
But i dont know how to run this macro on click of a button in core java application.
Is it possible to run ms-access macro on click of a button in core java application?

If not possible kindly suggest some other way.

Kindly do me a favour in this regard.

Thanks in advance.

Hello ebosysindia and welcome to the Java Programming Forums

I think your best option here would be to use a Visual Basic Script to run the Access Macro and execute this script from within your Java application.

I have done some research and I think you can use this VBS. It's untested though.
Obviousally change 'mydatabase.mdb' and 'macroname' to be correct. Save in notepad to C:\WINDOWS\system32 as runmacro.vbs

set objaccess = createobject("Access.application")
objaccess.opencurrentdatabase ("C:\mydatabase.mdb")
objaccess.run "macroname"
objaccess.CloseCurrentDatabase
set objaccess=nothing

Then you can use this Java code to run the script:

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
 
public class AccessMacro {
 
    private static void createAndShowGUI(){
 
        JFrame frame1 = new JFrame("Run Macro");
        frame1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 
        JButton button = new JButton(" >> JavaProgrammingForums.com <<");
        // Add action listener to button
        button.addActionListener(new ActionListener() {
 
 
            public void actionPerformed(ActionEvent e) {
                // Execute when button is pressed
                try{
                Runtime.getRuntime().exec("cmd /c start runmacro.vbs");
                }catch(Exception a){
                    System.out.println("Problem opening runmacro script!");
                }
            }
        });
 
 
        frame1.getContentPane().add(button);
        frame1.pack();
        frame1.setVisible(true);
    }
 
    public static void main(String[] args) {
        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                createAndShowGUI();
            }
        });
    }
}

Let me know what happens!

Originally Posted by JavaPF

Hello ebosysindia and welcome to the Java Programming Forums

I think your best option here would be to use a Visual Basic Script to run the Access Macro and execute this script from within your Java application.

I have done some research and I think you can use this VBS. It's untested though.
Obviousally change 'mydatabase.mdb' and 'macroname' to be correct. Save in notepad to C:\WINDOWS\system32 as runmacro.vbs

Then you can use this Java code to run the script:

import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import javax.swing.JButton;
import javax.swing.JFrame;
 
public class AccessMacro {
 
    private static void createAndShowGUI(){
 
        JFrame frame1 = new JFrame("Run Macro");
        frame1.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
 
        JButton button = new JButton(" >> JavaProgrammingForums.com <<");
        // Add action listener to button
        button.addActionListener(new ActionListener() {
 
 
            public void actionPerformed(ActionEvent e) {
                // Execute when button is pressed
                try{
                Runtime.getRuntime().exec("cmd /c start runmacro.vbs");
                }catch(Exception a){
                    System.out.println("Problem opening runmacro script!");
                }
            }
        });
 
 
        frame1.getContentPane().add(button);
        frame1.pack();
        frame1.setVisible(true);
    }
 
    public static void main(String[] args) {
        javax.swing.SwingUtilities.invokeLater(new Runnable() {
            public void run() {
                createAndShowGUI();
            }
        });
    }
}

Let me know what happens!

You means to say i have to make macro in vb code.

Originally Posted by ebosysindia

You means to say i have to make macro in vb code.

No. We will use a VB script to run the Access Macro. We then use the Java application to call the VB script.

Everything you have already done will remain the same.

O.K. Thanks for suggestion this one.

Originally Posted by ebosysindia

O.K. Thanks for suggestion this one.

Let me know if it works. Thanks

O.K. I will know you if it is success.

You might want to start first by making the runmacro.vbs script and running that to see if it works.

Thats the most important bit before the Java application. Once the vb script is working, everything else is easy.