Solved

Copy from Excel to Filemaker pro 5

Posted on 2004-10-18
12
836 Views
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?
0
Comment
Question by:skpd1978
  • 6
  • 5
12 Comments
 
LVL 28

Expert Comment

by:lesouef
Comment Utility
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.
0
 

Author Comment

by:skpd1978
Comment Utility
Is dynamic link possible with MS Access?
0
 

Author Comment

by:skpd1978
Comment Utility
OR Is it possible to build up a VB Interface to transfer data from excel to Filemaker Pro 5?
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
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.
0
 

Author Comment

by:skpd1978
Comment Utility
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?
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
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.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 

Author Comment

by:skpd1978
Comment Utility
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?
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
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.
0
 

Author Comment

by:skpd1978
Comment Utility
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?
0
 
LVL 19

Expert Comment

by:billmercer
Comment Utility
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.
0
 

Author Comment

by:skpd1978
Comment Utility
Can you give me the syntax how can I use this "Paste Append"  statement in my VB code?
Thanks.
0
 
LVL 19

Accepted Solution

by:
billmercer earned 500 total points
Comment Utility
Try this:
docommand.runcommand acCmdPasteAppend

Check the Access help documentation for more info.
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
PDF In Filemaker GO 3 1,018
Multiple statements in a calculation 4 700
FMP auto filed input? 3 113
Filemaker Server as a Web Portal - Licensing 3 106
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…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now