Calling InfoPath from Excel Macro

rconverse

Well-known Member
Joined
Nov 29, 2007
Messages
1,187
I'd like to find a way to automate looping through a folder and converting all InfoPath forms into Excel spreadsheets. There's a reference to InfoPath in Excel, so I am thinking there has to be some way to automate this, but I can't figure out how to open a form and export it to Excel.

Anyone else try to take on this task?

Thanks,
Roger
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Hmm. I'm not giving up just yet. Maybe I wasn't clear with my initial post. InfoPath is an application that I've stumbled onto. Using its capabilities with SharePoint is a very attractive opportunity right now. In order for this revised process to work as well as possible, it would be really great to be able to automate the conversion of the InfoPath files to Excel worksheets.

When you open an InfoPath form, under the file menu one of the selections is export to Excel. As I stated in my initial post, Excel holds references from InfoPath. Using VBA, I can't figure out how to:

1.) Open the InfoPath file
2.) Export to Excel

I'm pretty sure I can handle the rest. Anyone at all able to help me?

Thanks,
Roger
 
Upvote 0
Hello,

I can not help you with the code but I had similiar problems. Here is what I know:
1. Infopath can not directly submit to excel.
2. I have submitted it to sharepoint but need to manually update it to excel.
3. I also have submitted to access and using a pivot table (external Data) in excel to pull data out of access with the refresh button. To do this you will need to set up an ODBC.
4. Here is an infopath site that I have found helpful:
http://www.infopathdev.com/

HTH,
 
Upvote 0
Thank you for the site link. I'll check it out.

I am not looking to submit directly to Excel. I'm looking for a VBA solution so that I don't have to go into every form and export to Excel and then load to MS Access.

How are you submitting directly to Access? Currently I am submitting to a list on SharePoint in order initiate a workflow. I assume submitting right to Access wouldn't spark the workflow. I'm not a SharePoint expert either.

Thanks.
 
Upvote 0
Here is the work flow for submiting forms to access.

The user fills out and submits form.
Form is submited to access and Sharepoint site.
Excel workbook with pivot chart is opened.
User clicks red exclamation on pivot chart.
Chart is updated thru ODBC link.

All lines in blue is what is needed to be done manually.
_______________________________________________________________
Another way to do this so that you do not need to open each form individually is without using access is to:
Open form
Ø [File]
Ø [Export][Microsoft Excel]
Ø [Next]
Ø [Forms Fields and This Table or List]
Ø [Next]
Ø Uncheck all unwanted fields
Ø [Next]
Ø [Export Data From this form and these additional forms]
Ø [Add]
Ø [My Network Places]
Ø [“Your Location of Forms on Sharepoint”]
Ø [“Your form Name”]
Ø Right click gray bar [Modified]
Ø Click [Modified] 2x (Newest data on top)
Ø Highlight forms modified yesterday to day after last day updated
o Hold shift to do this
Ø [Ok]
Ø [Finish]
Ø An excel spread sheet should open.
Ø Copy spreadsheet to your workbook
<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:eek:ffice:eek:ffice" /><o:p> I am a novice in code but maybe somebody would be able to set up the steps above in code.</o:p>
<o:p></o:p>
<o:p>HTH</o:p>
 
Upvote 0
^That last bit helped. I couldn't multi select from SharePoint for some reason in the dialogue box created when I went to add more forms. I had to go and select each one. If I copy the forms off SharePoint into a folder on my network or computer, I can select them all at once and export them to Excel. Much easier than the alternative.

Thanks!
Roger
 
Upvote 0

Forum statistics

Threads
1,213,553
Messages
6,114,279
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top