Solved

Copy from Excel to Filemaker pro 5

Posted on 2004-10-18
12
837 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
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.
0
 

Author Comment

by:skpd1978
ID: 12342564
Is dynamic link possible with MS Access?
0
 

Author Comment

by:skpd1978
ID: 12342774
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
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.
0
 

Author Comment

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

Expert Comment

by:billmercer
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

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

Expert Comment

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

Author Comment

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

Expert Comment

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

Author Comment

by:skpd1978
ID: 12353772
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
ID: 12358197
Try this:
docommand.runcommand acCmdPasteAppend

Check the Access help documentation for more info.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
FileMaker Pro Reporting on relational database 4 451
FMP auto filed input? 3 121
Filemaker Pro 13 Adv - Label printing 17 286
Simple Search Script in FileMaker 4 32
Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Concerto provides fully managed cloud services and the expertise to provide an easy and reliable route to the cloud. Our best-in-class solutions help you address the toughest IT challenges, find new efficiencies and deliver the best application expe…

937 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

7 Experts available now in Live!

Get 1:1 Help Now