Need some help with exporting from FM and maintaining records in Excel

[Arbs001]

Hello all,

I am having a bit of trouble figuring out a way to obtain my goal.  My goal is to use Filemaker as a database for contacts, and then, upon the pressing of a button, exporting that contact info into an already existing Excel file, in which it would either add a new entry into the database, or modify an existing one based on the name.  I am essentially using both Filemaker and Excel as the same database, but Filemaker modifies Excel, and not the other way around.  

 

My attempts from my limited Filemaker usage have been to Export the filemaker document as an excel workbook.  So therefore, it would just re-save the database, and any changes would be changed because everything was.  HOWEVER, that has proven to be ineffective, as Filemaker is not letting me export the same thing twice.  It needs a new name.

 

My next attempt was to export the Filemaker database as a .csv, and to have the excel file automatically update upon opening.  This works, except for there are no headers, such as the name of my fields.  I tried the Save as Excel file option, and it saves every field, out of order.  I have extra fields in there that I don't want.

 

Is there some way to combine the effects of Save as Excel file, with the specifications of what you want to export using the Export command?

 

Thanks

[comment]

There is no native way for Filemaker to update an existing Excel spreadsheet. There might be a plugin that provides such functionality (I don't really know).

If you can script this from Excel, so much better. Filemaker can provide field names with its export, if you export in one of these formats: merge, XML, dbf and, of course, Excel (note the difference between exporting as Excel and saving as Excel).

Edited November 20, 2015 by comment

[Arbs001]

How could I export it over and over, if it won't let me replace the filenames though?  I have no problem pulling from excel vs. pushing from Filemaker.  The main problem I have is that when I click the button I made, it executes the Export script, and I get an error saying that the file name already exists, or the hard drive is locked

[Wim Decorte]

Do you have the option "automatically open" toggled on for the export?

The export will just overwrite the file if it is there, but not if it is locked by some other process.

If you are proficient with OS-level scripting then you can write VBscript or PowerShell code (your profile says Windows) that can take the FM exported data and update the Excel sheet.  No plugin required.

[Arbs001]

Hmm good point about it being locked.  As of now I export as .csv, made my own column headers, and import a row below them. 

 

Thanks for the help

[Kris M]

You might be able to set FM up as an odbc dsn data source and leverage Excel/VBA to get the data.

https://www.simple-talk.com/sql/database-administration/getting-data-between-excel-and-sql-server-using-odbc--/

My only question is what do you need Excel to do that FM can't?

Edited November 20, 2015 by Kris M