Field names with a . in them eg PO.Items

[Ste4en]

I need to export an excel spreadsheet file from a query or table made by a query and the field names need to be PO.Item or say PO.Company. Access will not let me use a . in a field name is there a work-around?

Thanks
Steve

 

[Jack Cowley]

I would not put in the dot. POItem works fine but if you have to have something then PO_Item will do the trick.

hth,
Jack

 

[Ste4en]

I have to have the DOT. The excel spreadsheet is used to upload into a Client's system and he wants that format with a DOT.
Can I do it?.

 

[Fizzio]

Yes, but it will be difficult!

You will need to export as an allowed field name eg PO_Item as Jack Suggested then open the exported file, search for the field names and replace _ with .

Not a task to be envious of!

 

[Mile-O]

Run a macro in the spreadsheet to change the field names.

 

[namliam]

You CANNOT export it from access with a DOT. a dot is used to seperate the table and field names! Thus cannot be used the way you want.

What you can do is export it POPUTADOTINHEREItem or something like that and later in Excel replace PUTADOTINHERE by a . the changes of PUTADOTINHERE beeing a valid text in the export are slim to none....
You can even automate it if you so desire....

Or export it to excel without column headings and put the columnnames in only in Excel (also automatable...)

Regards

 

[Ste4en]

Many thanks - at the moment I do run a macro in Excel to rename all of the columns to add a DOT. I was just trying to remove that step. I will pursue the client and see if they can remove the need for a DOT.
thanks again

 

[namliam]

You can "remove" the step by automating it in the Access export procedure...

Regards

 

[Ste4en]

Tell me how...... please

 

[namliam]

First you need to go to a module in Access
then in the menu: Tools => References
Find: Microsoft Excel x.y object library
(where x.y is some number dependand upon the version you have)

Now go to excel open a new sheet and RECORD the stuff you normaly do. (including file open and close and stuff....). After you closed the file hit ALT+F11 to go to the VBA editor in Excel.

Find the code you just recorded
Select it all (exept the "Sub... " and "end sub" lines
Now go back to access
Find the export routine
At the top (right after sub...) add:

    Dim xl As Excel.Application
    Set xl = New Excel.Application

then after the export to excel add

    With xl
'paste your code from excel here
    End With
    Set xl = Nothing

And finaly insert a . before every statement you pasted. So to open the file in excel would be:
.Workbooks.Open FileName:="C:\YourFile.xls"

Hope you can figure it from here, if not post your question and we will go from there...

Regards

The Mailman

 

[Ste4en]

Many thanks, worked out just fine.
Steve