Set the Column/Length format on an Excell sheet

[Jeffery B Paarsa]

Hello,

When I import an Excel spread sheet to my Access DB table, each Column on my
Access Table gets the default Field Size of 255 with the format of @... How
can I set the lengths of a column to an specific size and format, i.e. 40
char. or Telephone number, or ?

I tried difrent combination of the following with no success:

Sub FmtCell()
Range("A1").EntireColumn.Format.Text = ("Text")
Range("B1").EntireColumn.Format.Special ("Phone")
End Sub

 

[John W. Vinson]

Hello,

When I import an Excel spread sheet to my Access DB table, each Column on my
Access Table gets the default Field Size of 255 with the format of @... How
can I set the lengths of a column to an specific size and format, i.e. 40
char. or Telephone number, or ?

I tried difrent combination of the following with no success:

Sub FmtCell()
Range("A1").EntireColumn.Format.Text = ("Text")
Range("B1").EntireColumn.Format.Special ("Phone")
End Sub

Create the Access table *first* with the desired field properties, and import
into that premade table, or else use File... Get External Data... Link to link
to the spreadsheet and then run an append query to migrate the data into the
predefined table.

 

[Jeffery B Paarsa]

I did not used the External data but any time I used Import I noticed that
Access First deletes the table I have created then it rebuilds it again based
with the default Column size of 255 for Char/General...

I try Link method and see what happens.... I though via a Macro I could set
the size of the Excel column and it's format if possible but I don't know
how...

 

[John W. Vinson]

I did not used the External data but any time I used Import I noticed that
Access First deletes the table I have created then it rebuilds it again based
with the default Column size of 255 for Char/General...

I try Link method and see what happens.... I though via a Macro I could set
the size of the Excel column and it's format if possible but I don't know
how...

Access has "strong datatypes" - Excel doesn't.

You can use the TransferSpreadsheet action from the macro to migrate the data
into an existing table.

 

[Jeffery B Paarsa]

Using Link method does not work either. It changed the size back to 255.

 

[Jeffery B Paarsa]

Thanks, but how that one is done? I have no idea about that?

 

[John W. Vinson]

Using Link method does not work either. It changed the size back to 255.

But if you append the 255 byte (linked, external) text field into a 24 byte
(stored, table) text field, it will store the 24 bytes.

Take a look at the VBA help for TransferSpreadsheet.

 

[Jeffery B Paarsa]

Thanks, but I did a search for TransferSpreadsheet and it came back with a
page with that word on it when I cliecked on it, it returned page not found!
http://office.microsoft.com/search/redir.aspx?AssetID=XT011332961033&CTT=5&Origin=HP011359531033

 

[John W. Vinson]

Thanks, but I did a search for TransferSpreadsheet and it came back with a
page with that word on it when I cliecked on it, it returned page not found!
http://office.microsoft.com/search/redir.aspx?AssetID=XT011332961033&CTT=5&Origin=HP011359531033

What version of Access? There's a good *local* (not web) help topic in A2003
if you type Ctrl-G to get into the VBA editor, press F1, and search for
TransferSpreadsheet. Don't know about 2007.