Download: March 2007

Community Comments

Excel Is Excellent as Go-Between App; CD Snafu; Outlook Express Settings

By Cheryl Leff

Q:
My company makes parts for machines that are sold to many other companies. My salespeople travel around the country calling on client companies, both to sell them parts and to gather information on how many parts of each type will be needed over the next several quarters, so that we can plan how much of each part to manufacture. A couple of years ago, we hired a consultant to construct a customized manufacturing and inventory control database for us in Microsoft Access. However, this database resides only on the network in our plant.
     We'd like our salespeople to be able to collect planning data to go into this database; however, they don't have Access on their laptops, just Word and Excel. Is there a way we can have them collect the data in Excel and then pass it over to the database?

A:
Yes, indeed. Excel is an outstanding “go-between” program that can import and export data to and from many other applications.
     First, you need to determine what data you want to collect and what table or tables it will go into in your Access database. Then, construct an Excel worksheet that has the same information, and in the same order, as the Access table. Use the first row of the selected range of the worksheet for labels that are the same as the field names in the Access table, e.g., CompanyName, PartNo, DateNeeded, QtyNeeded, etc. Make sure the data type for each column in the spreadsheet is compatible with the data type of the Access field into which you plan to import it – for instance, date formats should be defined in a similar way in both, or they may undergo some strange transformations. Number formats of Access fields should be defined similarly to any Excel number or currency formats (for instance, with or without two decimal places). Save the selected cell range as a CSV or TXT file. (If these file types don’t show up in the “Save as type” window, you may need to re-insert your MS Office installation disk and add the File Converters component to your installation.)
I suggest that you establish a folder on the network where everyone can put a copy of their worksheets, with a consistent naming scheme so you know what each file contains – for example, in a folder called Sales Estimates on a network drive, Joe might save his spreadsheets with filenames that identify him and the time period, e.g., Joe2007Q2. That way, the person responsible for maintaining the database will know which files to import.
     In the Access database, click File > Get External Data > Import. Browse to the directory with the salespeople’s estimates; select “Text files (*.txt, *.csv, *.tab, *.asc,)” in the “Files of type” window, then select the desired file and click OK. The Import Text Wizard will open with the selected file. In the first window, make sure “Delimited” is selected. then click Next>. In the second window, make sure “Comma” is selected as the delimiter, and check the box by “First Row Contains Field Names” if you saved the names with the data. In the next window, choose to transfer the data to an existing table (assuming you already have a table set up to receive the data) and select the appropriate table. The lower window shows a preview of how the data will be imported. If it all looks right, click Next and then Finish. Repeat the same process with each file you want to import. 

Q:
My father has a PC running Windows ME and has recently tried saving files to a blank CD-R. He just gets an error message that says "insert blank disc into drive M." He is able to load and run software from his CD drive M. What is he doing wrong?

A:
While there could be other causes, my best guess, based on the information you gave me, is that he may have the disk in upside down. I have bought several brands of blank CDs that have no markings on either side and have actually been packaged upside down. If you insert a disk in the tray and the program still tells you to insert a disk, try taking it out and flipping it over. If that doesn’t do it, you may have the wrong kind of disk for the drive, or the disk or drive may be damaged.

Q:
I have recently purchased a new flat screen HP computer. I am currently using Outlook Express, which was what I was using on my old computer. However, the only way I can receive my messages is to hit the Send/Recv buttom. How can I change this to receive my messages automatically? Plus, the icon in the lower right-hand corner alerting me that a message has been received does not work, possibly because of the Send/Recv problem.

A:
You can set your Send/Receive options in OE to check the server when you open the program and you can set how often it should recheck the server.
     Go to Tools > Options and select the General tab. In the middle of the dialogue window, under Send/Receive Options, put a check in the box by “Send and receive messages at startup.” Just below that, check the box labelled “Check for new messages every ___ minutes,” and set how often it should check. Then click OK to close the dialogue. This should also activate the icon at the bottom right.

• Share
  • Digg This
  • del.icio.us
  • Newsvine
  • Facebook
  • Reddit
  • Furl It
  • !Y My Web
  • Google
• Email
• Print

Recommend This

Community Comments