• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 857
  • Last Modified:

Copy from Excel to Filemaker pro 5

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?
  • 6
  • 5
1 Solution
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.
skpd1978Author Commented:
Is dynamic link possible with MS Access?
skpd1978Author Commented:
OR Is it possible to build up a VB Interface to transfer data from excel to Filemaker Pro 5?
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.

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.
skpd1978Author Commented:
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?
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.
skpd1978Author Commented:
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?
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.
skpd1978Author Commented:
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?
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.
skpd1978Author Commented:
Can you give me the syntax how can I use this "Paste Append"  statement in my VB code?
Try this:
docommand.runcommand acCmdPasteAppend

Check the Access help documentation for more info.

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now