Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Copy from Excel to Filemaker pro 5

Posted on 2004-10-18
Medium Priority
Last Modified: 2011-10-03
How to copy perticular cell from MicroSoft excel sheet and paste it in perticular text box of already existing database file in Filemaker Pro 5?
Question by:skpd1978
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 5
LVL 28

Expert Comment

ID: 12341174
I guess you do not mean the basic copy/paste...
If you  mean a dynamic link, you can't.
The closest thing you can do is to share yr excel file as a ODBC source and have Filemaker to fetch the requested data via a SQL query.
Otherwise, there may be a way using DDE commands if you are familiear with this.

Author Comment

ID: 12342564
Is dynamic link possible with MS Access?

Author Comment

ID: 12342774
OR Is it possible to build up a VB Interface to transfer data from excel to Filemaker Pro 5?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 19

Expert Comment

ID: 12343843
There are a couple of ways to do this, which is best depends on your project.

If you just need a quick way to get a value from an Excel cell and bring it into Filemaker, copying and pasting is easy. There's a way that doesn't require knowledge of DDE, or even much programming.

Create a new Excel document with a cell that references the value of the cell you want from your existing spreadsheet. In this new worksheet, record a new macro that selects the cell and copies it to the clipboard, then closes the worksheet. Save this macro with the name auto_open.

Now, from within Filemaker, create a script that uses the Send Message command to open your new Excel worksheet. The worksheet opens and immediately copies the cell contents to the clipboard. Now you can paste the contents of the cell into any Filemaker field.

You can use another script step to do the paste. You will need to put a short delay in the Filemaker script to give your Excel file time to run.

If you need to bring larger amounts of data from Excel into Filemaker, or if you want to get the data from the worksheet without actually running Excel, then ODBC is a much better way to go. It's not that hard to set up, and will be more reliable.

You could create some sort of dynamically updated link with Access, but I think it would involve either DDE or coding in VBA. You can do a "paste as link" onto an Access form, but I don't think it's a dynamic update. You'd probably still have to do some VBA coding.

There are other ways to handle this, including using a third-party scripting language like AutoIt or WinBatch.

Author Comment

ID: 12343918
Hi billmercer

Thanks for reply. I am new to filmaker. I think from options suggested by you ODBC seems to be useful for me. Can you please explain in detail how can I do this?
LVL 19

Expert Comment

ID: 12349549
Here's a step-by-step procedure to create an ODBC connection to an Excel file and get the data into FileMaker. It looks long, but I've put in a lot of detail. After you've done this a few times, you can do the whole process in a minute or two. I'm using FileMaker 6 on Windows XP. The steps may be a little different, depending on your own versions.

First Step: create an Excel file to get data from.
Create a new Excel file with column headers and data that looks like this:
 First         Last          Phone
 Joe           Schmoe         123456789
 Jane          Doe           999888777
 John          Doe           444333222
 Sam           Sloe           888888888

After entering the data, highlight the range, including the column headings.
Create a Range Name by choosing Insert|Name and giving it the name PhoneList
Save this file as ExcelDemo.xls

Second Step: Create an ODBC DSN for your Excel file. A DSN is sort of like a shortcut that contains information needed to connect to an ODBC database.
Open the ODBC Administrator (in Windows XP, that's under Administrative Tools on the Start Menu)
There are several types of DSNs available. We will create a System DSN.
Click the SYSTEM DSN tab, then click the ADD button. You will see a list of drivers.
Scroll through the list until you find the Microsoft Excel driver. Highlight it and click Next or Finish.
Type a name for the ODBC connection. Use "Excel ODBC Demo" as the name.
You will be prompted to choose the version of Excel and the specific workbook.
Unless you have a very old version of Excel, you'll probably use the default value.
Click the Select Workbook button. A dialog box will prompt you for the location of the Excel file.
Browse to the location of the ExcelDemo.XLS file, highlight it and click OK to return to the DSN configuration window.
Click OK again. You will now see a DSN called Excel ODBC Demo in the list of ODBC connections.

Third Step: Get data from your ODBC connection into Filemaker.
Open the Filemaker database you want to bring the data into. I created a test database with First, Last, and Phone fields.
Go to the File Menu and choose Import Records|ODBC Source.
You should see a list with at least one entry on it, labeled "Excel ODBC Demo". (If you don't see this entry, you may have made a mistake while creating the DSN. Go back and check your steps)
Click this entry and hit OK.
You may be prompted for a username and password. In this example, there is no username or password, so leave them blank, and click OK.
Now you should see the SQL Query Builder. This will help you create a SQL query to get the data you need from your Excel file. (If you're familiar with SQL, you can create your own query by typing in the query window instead of clicking the buttons.)
On the left side of the dialog is a list of tables. In this case, the table name is the RANGE NAME you provided earlier, so you should see an entry labeled PhoneList.
(Notice that the "table" is NOT the same as the whole Excel table, it's just the selected part you gave a name to. You can actually have more than one database in a single Excel worksheet this way.)
Click this entry, and you should now see a list of fields on the right side. Double-click on the fields you want to include in this query. In this case, we want all three,
so double click each one from top to bottom. This will include all records from the Excel file in the import.
Next, we will restrict the query to only import the number for someone whose last name is DOE. If you just want all the data, you can skip this part and click EXECUTE.
Click the WHERE tab. Choose the Column Name, select the Last column. Click on Operator, and choose =. Click in the Value area, and type Doe, then click the Insert button. Now you have created a SQL query that will return only the information for people whose last name is Doe.
Click the EXECUTE button to run the query.
Next, you will see the normal FileMaker import screen. Here you can select which fields from your query will be imported into which FileMaker fields.
If the field names in FM match those in your Excel file, the import will automatically match them up for you. If not, you may have to make changes.
Click IMPORT to bring the data into your FileMaker table.
Now look in your FileMaker table, you should see the values from the Excel file.

Depending on your needs, you can create new FM records, replace existing records, or update only certain records by matching up names or numbers. This approach is very flexible and powerful. You can use a script step to activate the import, or even specify a SQL query that is stored in a FileMaker text field. That means you can use a single script to run many different queries. Depending on your version of FileMaker, you can do even more elaborate things.

Author Comment

ID: 12350458
Thanks a lot. Yes It should work. But my point of view is like this. I have made a command button on Excel sheet. And I have some code written behind that command button which can copy multiple cells on perticular sheet of activeworkbook and paste it in a perticular Filemaker pro file respectively. Is this possible with filamker pro? Or I can use this option with MS Access?
LVL 19

Expert Comment

ID: 12351784
If you mean you want to copy multiple columns from Excel and paste them all at once directly into separate fields in a FileMaker table, the answer is no, you can't. You can do the equivalent by pasting the Excel data into a single field, then using a script to bring the values in by importing, but that requires a short script, or a couple of buttons.  

You can do this with Access, but not with the normal Paste operation. You have to use a separate option called Paste Append. That will paste tab-delimited text into new records in your table. You can't paste over existing records this way, only add new ones.

Author Comment

ID: 12351972
Suppose I use MS Access,then I will have two arrays copied from perticular sheet on excel in which one contains data headings and other data. How can I use this "Paste Append" Option to paste those arrays in MS Access database as new record?
LVL 19

Expert Comment

ID: 12352125
The paste append does not pay any attention to the headings, it just inserts one new record in the database for each row you copied from Excel. Access will paste the data into the fields in the same order they were in Excel, so that means you need to have the fields in the same order in both the Excel and Access tables. The first selected cell goes into the first field, the second cell goes into the second field, and so on. To keep the headings from being pasted as new records, you will need to select just the data, and leave out the headings.

Author Comment

ID: 12353772
Can you give me the syntax how can I use this "Paste Append"  statement in my VB code?
LVL 19

Accepted Solution

billmercer earned 1500 total points
ID: 12358197
Try this:
docommand.runcommand acCmdPasteAppend

Check the Access help documentation for more info.

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Problem: You have a hosted FileMaker database and users are tired of having to use Open Remote or Open Recent to access the database. They say, "can't you just give us something to double-click on rather than have to go through those dialogs?" An…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question