Importing Data from Access to Excel 1

[RacerGirl117]

I have created a query that pulls data into an existing worksheet from Access 2000. There are a couple of fields in the query that have forced upper case (in Access), but when the data is pulled into Excel some of it is no longer in upper case format.

One of the fields is a combination of numbers and letters, and the other one is letters only. Is there a way to force both columns to display letters in upper case?

Thanks in advance, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

 

[lhite]

Are you forcing the upper case with the object's format? If so, try using the UCase() function instead.

If that doesn't help you can insert another column in the Excel worksheet, insert a formula like =UPPER(cellno), then paste that down the column. Select the column, COPY, and EDIT, PASTE SPECIAL, VALUES. You can then delete the original column with the lowercase numbers.

-Larry

 

[RacerGirl117]

Larry,

I was trying to do it in the Worksheet, Activate in VBA but couldn't get it to work.

I was using (for example)
UPPER (C2:C380)
but apparently you can't do multiple cell references in VBA with Excel.

I also tried
=UPPER(C2)
directly in the cell but it didn't like that either.

I then tried something similar to your last idea, but ran out of time to really play with it. I'll do some more with it later tonight and get back to you if I need to.

I thought for sure there was a more efficient way to handle this situation in Excel.

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

 

[jshulman]

It sounds like in Access it is forced to upper case in field set up (formatting) but can really be entered in lower case.

So in your query, use UCASE(fieldx) to change the field to upper case. It should then output it as upper case.

hope this helps.

judy.s

 

[RacerGirl117]

Judy,

Thank you very much. That helped, but I'm still having problems getting this data into the darn worksheet.

This is how I'm attempting to get the data..
1) I open the worksheet I want to import the data into
2) I go to Data, Get External Data, Run Saved Query
3) I select the appropriate query from dialog box
4) I leave Existing worksheet selected
5) I click on the Properties button and deselect the Include Field Names and Adjust Column Width boxes.
6) Both Query Definition boxes are checked.
7) Enable background refresh is checked.
8) Both "Preserve" boxes are checked.
9) Insert cells for new data, delete unused cells box is checked.
10) I click OK
11) I leave the cell destination alone (cell C1 is designated as default) and click OK

Here's what happens...
If I select the cells I want to overwrite I get this message:
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

If I put the cursor in the top left cell of the data I want to overwrite I get this message:
The start of the destination range specified lies within an existing External Data Range Please choose another destination.

Now, if I insert a blank row, save the worksheet, and then reopen it, I get the "ODBC" message and then this:
The following data range failed to refresh:
qryJobs
Continue to refresh all?

Regardless of whether I click OK or Cancel, nothing happens.

If I select the 4 columns in which I want to replace/update data, then go to Data, Get External Data, Run Saved Query, open the query I want, deselect "Adjust column width", select "Overwrite existing cells with new data, clear unused cells", and leave "Include field names" selected, then click OK it seems to work okay but then I have to reformat the column names for those four columns.

This has been a very frustrating experience for me, having never imported from Access to Excel before. If anyone can be of assistance here, I sure would appreciate it. I have been unable to find any useful documentation on this subject.

Thanks in advance, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

 

[RacerGirl117]

Okay, now I'm getting the data in the worksheet okay. I had to delete all existing queries and recreate one to do what I wanted. I was apparently missing fields in the other fields, which is what was causing the error messages (I'm assuming).

But herein lies a new challenge. Let me tell y'all what this spreadsheet is used for. My company sells, services, and installs fire safety equipment (fire alarm systems, fire suppression systems, etc.). Currently, we have almost 400 active jobs (individual projects sold to customers). This spreadsheet is used to track the different phases of any given job (about 30).

I am pulling data from an Access 2000 database that fills in the basic job information (job number, change order number, job description/title, sales person, project manager, and customer). This list changes as jobs or change orders are entered/added to the database. How can I refresh the data in the spreadsheet without having to completely realign the existing data (all the dates that are entered)?

In the "If the number of rows in the data range changes upon refresh:" section of the External Data Range Properties dialog box it doesn't matter which option I select. If a new row gets added to the data range, it doesn't move the rest of the data down or up, whichever the case may be. I clicked the ? on the "Fill down formulas in columns adjacent to data" field but the explanation provided didn't make sense to me. Should I be selecting that? Are there any other options in this dialog box that I should be selecting?

I'm desperate! Please help!

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[JVFriederick]

Send me a copy of the MDB file and your XL file and I'll be happy to modify the query for you.

JVFriederick@Yahoo.com

 

[RacerGirl117]

JV, you've got mail coming your way. I made an mdb with just the info. you'll need. I sent over a zip file that has the mdb and a copy of the spreadsheet in question. If you can come up with anything, that would be great. I've been beating my head against the wall today. I thought I was practically done with this thing. I should have known better. Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com

 

[RacerGirl117]

Okay folks...I wound up converting the spreadsheet to Access for storage and data entry. What we'll wind up doing is running a query and letting Excel analyze and refresh the data. But now I'm having a problem with a particular field when it gets exported to Excel.

The Comments field which is a Memo field in Access does not carry into the spreadsheet once the data is refreshed. Is this due to some limitation that I am unaware of?

Thanks, Jessica Morgan
Fire Fighter Sales & Service Co.
Pittsburgh, PA

http://www.firefighter-pgh.com