Transferspreadsheet changing field name 1

[bustersports]

1st, thanks for taking the time to look at my question.

I am using Access 2010. Trying to transfer a table to Excel. All of the fields must be a text field, left justified, capitalized. This is working fine with the exception of one field name being changed. The field name in the exporting table is REFERENCE#. When it exports to Excel, it becomes REFERENCE. (with the period added, no pound sign). I have tried with the caption of the field blank as well as REFERENCE#, same result either way. I get the same result, without the #, when just doing a right click on the table and exporting to Excel. I have tried creating a new table, even just having REFERENCE# as the only field, same result. I have tried adding the # to other field names, get the period as well. Any suggestions for how to get around this? Deleting or changing the pound sign to some other character is not an option as the file is sent to another company which requires the pound sign be there.

My code for the transferspreadsheet is -
DoCmd.TransferSpreadsheet acExport, 8, "tblTempBizTalkOutputToCTSI", "C:\Freight Payment Tools\Exception Central\test.xls", False, ""

Below is the actual result (some fields deleted for readability here, they are all working fine)

CARRIER REMITZIP CURRENCY BIL2NO PRONUM BOLNUM HAWB PRODATE SHIPDATE REFERENCE. ZONE MODE

Here is what the result should be

CARRIER REMITZIP CURRENCY BIL2NO PRONUM BOLNUM HAWB PRODATE SHIPDATE REFERENCE# ZONE MODE

 

[xlbo]

Not used Access for a while but I seem to recall that # is a construct used for identifying dates e.g.

Select blah
from table
where datefield = #01/01/2013#

It may be therefore that the export cannot deal with that character properly

2 wortkarounds spring to mind:

1: after export, open the excel file using vba and change the column header
2: You *may* get some joy using a saved import/export - I think in there (if I remember correctly), you can manually specify the export field namee when you set up the template

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

I cannot find a way in Access 2010 to create an export specification.

I have also tried exporting using ExportWithFormatting and ImportExportSpreadsheet. Both keep the pound sign, but are making the column headings centered. The headings must be left justified. I am hoping to not have the user open the file to change the field name.

I have searched a couple of times here and on the web, cannot find anything to help.

 

[xlbo]

User wouldn't have to open the file - little bit of VBA will do it for you:

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.visible = TRUE ' comment out this line if you don;t want to see the workbook open - good to leave it there for debugging though

dim wb as Excel.workbook

set wb = xlApp.Workbooks.Open "Path of File with folder name", True, False

wb.sheets("SheetName").range("F1").value = "REFERENCE#" ' change to be the correct cell reference

wb.save (true)

wb.close

set wb = nothing

set xlApp = NOTHING

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

Hi xlbo,

I put this in my code, am getting the error Expected End of Statement error on this line - set wb = xlApp.Workbooks.Open "C:\Freight Payment Tools\Exception Central\test.xls", True, False, highlighting the file name. I confirmed I do have the Excel 14 referenced under Tools/References in vba.

Any ideas?

Thanks

Function ExportToExcelTekTips()

Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")

xlApp.Visible = True ' comment out this line if you don;t want to see the workbook open - good to leave it there for debugging though

Dim wb As Excel.workbook
Dim FileName As Variant

set wb = xlApp.Workbooks.Open "C:\Freight Payment Tools\Exception Central\test.xls", True, False

wb.sheets("tblTempBizTalkOutputToCTSI").range("AW1").Value = "REFERENCE#" ' change to be the correct cell reference

wb.Save (True)

wb.Close

Set wb = Nothing

Set xlApp = Nothing

End Function

 

[xlbo]

Try getting rid of the true/false part of file open - you only really need the path (others are optional arguments)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

Correction - I deleted the Dim FileName As Variant, I was checking something else. Does not correct the error message however.

I have it figureed out and working. I found another online discussion and determined what the problem is. Below is the code that does work properly. It opens the file, changes the heading, the saves and closes Excel.

Thanks very much to xlbo for supplying the bulk of the code.

Dim xlapp As Excel.Application
Set xlapp = CreateObject("Excel.Application")

xlapp.Visible = True ' comment out this line if you don;t want to see the workbook open - good to leave it there for debugging though

Dim wb As Excel.workbook

Set wb = xlapp.Workbooks.Open("C:\Freight Payment Tools\Exception Central\test.xls")

'set wb = xlApp.Workbooks.Open "C:\Freight Payment Tools\Exception Central\test.xls", True, False

wb.sheets("tblTempBizTalkOutputToCTSI").range("AW1").Value = "REFERENCE#" ' change to be the correct cell reference

wb.Save

wb.Close (True)

'Set wb = Nothing

'Set xlapp = Nothing

xlapp.Quit

 

[xlbo]

No worries - thanks for posting back with the working code. It does seem to be a funny quirk of how access exports to excel that it just can't cope with # in the field name although putting by db design hat on I would never allow that field name in my db in the 1st place

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

So I had another issue come up related to this request. We send the output file to an outside vendor, so we have no chance in getting some of the strange requirements changed.

In the exported Excel file, columns H and I are text fields (as are all the columns) but are actually dates. The correct output file would read as such:

PRODATE SHIPDATE
1/1/2012 1/1/2012 (of course dates will change)

What is being exported is below:

PRODATE SHIPDATE
[highlight #FCE94F]'1[/highlight]/1/2012 [highlight #FCE94F]'1[/highlight]/1/2012 with the apostrophe in the formatting, does not show in the field

The apostrophe is causing the vendor to reject the file when they attempt to import it.

Each output file name must be unique. Each file can have from only one to thousands of records. Multiple users create the files. All fields must be text and left-justified.

Since each filename is unique, having a macro (as far as I know but could easily be wrong) autorun would not work. Is there a way to make this work without having to have the user open each file and manually change the fields?

Thanks very much again.

 

[SkipVought]

hi,

It is being exported as TEXT, so THAT is why the APOSTROPHY is prepended.

Otherwise, Excel will CONVERT the string to a date when it is imported, using m/d/yyyy as the conversion assumption.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

What skip said!!

All in all you may be better off controlling this. All on vba. You already have code to open, amend and save a file - you could fairly simply run the data extract from there as well this removing any limitations of transfer spreadsheet.

That way it's all fully controlled from central (access) source

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

Hi Skip,

Yes it is being exported as a text file. I have tried transferspreadsheet and export with formatting. I have not been able to get the export to show the date field in the date format, 1/1/2012 and NOT put the apostrophe and still be left-justified. Any other suggestions?

 

[SkipVought]

But, why don't you try to IMPORT the file into Excel, using the appropriate date conversion of that file rather than OPENING the file with Excel.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[bustersports]

Skip,

Maybe I am missing something about doing the import. If so, please let me know what it is. One of the objectives is to avoid the user having to open Excel and do anything with the data, just provides too much potential for error.

I can get the data exported, Excel opened, data populated, just cannot get the date fields to show as 1/1/13 vs. [highlight #FCE94F]'[/highlight]1/1/13. I have setup a macro in Excel, which I can sometimes get to fire. But it seems to be a problem if it is not in the file being opened, being in PERSONAL.XLSB does not seem to make a difference, as sometimes it runs and sometimes it does not.

Any further suggestions from anyone would be appreciated.

Thanks.

 

[SkipVought]

So how are YOU getting the data into Excel now?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[xlbo]

Weird - thought I'd posted again with a link to controlling data extraction from access to excel using recordsets rather than transferspreadsheet but obv not

In any case, this may help...

An addition to the field re-name code above:

application.TransitionNavigKeys = False

This stops excel putting an apostrophe in front of text fields

see if that fixes it for ya

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[bustersports]

Currently the data is received in a spreadsheet, it has to be updated and data added in certain instances. By allowing each user to enter data, there has been multiple instances of data being entered in the wrong format. Setting field requirements in Excel has not worked. In addition, data from the spreadsheet is added to another, basically a summary. That is what the Access tool would do for the user, verify data integrity, do the data summary.

 

[bustersports]

xlbo,

Two questions.

1. First, using application.TransitionNavigKeys = False, can it be limited to only certain fields as all other fields, excluding these 2 date fields, are text and must be read as such (or will this not be a problem)?
2. I tried to run it, am getting a "Method or data member not found" error. I have Microsoft ActiveX Objects 2.1 library and Excel 14.0 Object library checked under references in VBA. Am I missing a reference that needs to be checked?

Thanks,

 

[PHV]

"Method or data member not found" error
Use your Excel.Application object, eg:
xlapp.TransitionNavigKeys = False

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[xlbo]

In your case it will depend on your code

Rather than application. Use whatever you have defined your excel application as (commonly xlapp)

On terms of what it does, it is a global setting that stops the automatic usage of apostrophes before text. You can open a file, turn it on, dm save the file then turn it off again if you want. Please note that this is not tested but I thought it was worth a shot before messing around with recordsets

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question