DoCmd.TransferSpreadsheet method question

  • Thread starter Thread starter Stuart
  • Start date Start date
S

Stuart

I would like to use this method to 'automate' the import of an
Excel workbook.

I wish to bring each worksheet into its own table (with the
table name = to the Sheet name).

Neither the Sheets nor Tables need to be linked in any way.

I need to import each Sheet completely, and row 1 in each
Sheet contains the field names.

Column B in each sheet contains (generally) a mass of text
data. My understanding is that the cell in each worksheet
with the greatest amount of text, will effectively set the
RowHeight for ALL records in that particular table. There
is no way round this?

Would someone be kind enough to give me a start with a
little code, or perhaps a link to some good reference,
please?

Regards.
 
Public Sub ImportSpreadsheet(strTable As String, strFileName As String, _
strWorkSheet As String, strRange As String)
'Purpose: to import a specific sheet and range of a workbook
'Usage: ImportSpreadsheet
"tblExcel","c:\temp\Book1.xls","Sheet1","C5:D7"
'True in the options means the top row are field headers
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, True, strWorkSheet & "!" & strRange
End Sub
 
Many thanks for the code. Very kind, and much more
than just a start.
If you can help further, it would be a bonus.

I'm importing 15 Fields from the Xl sheets.

Fields 1-4 will contain "Client Data" ie non-adjustable by a user,
and mainly textual.
Fields 5-9 will contain "User Data" from their input in Xl, (totally
numeric) which they will be free to alter under Access.
Fields 10-15 (also numeric) are reserved for computation when
the sheet is returned to Xl.
(I'm assuming I will find a way to protect 'reserved' Fields from
my users).

The main issue revolves around the Client Data in Field 2. This
data is textual and (sometimes) massive. My limited research
suggests a Memo field. However, that sets the rowheight for
every Recordset (as best I can see). The Field2 data is the only
field like this.....all other data seems to fit fine.

I'm considering the following possible solution:

Before import, the Xl B2 cell has Wraptext enabled. If I can
'split' that cell, such that the data would then occupy sufficient
rows, I could achieve the following record, before the import:

A B C etc
D Sample data in 20
cell "B2" when
split across a
few rows.

After import, I think that each Xl row would have been treated
as a Recordset. If so, then probably 50% of the Recordsets
will look as the above example, whilst the remainder would be
occupying just one Access "row".

I HAVE to display the colB/Field2 data, since its information is
critical to the decisions made by users out in Fields 5-9.

Bearing in mind that no calculation is performed under Access
(purely, [ I hope] a means to reliably share the data for multi-
user input) and that the data will then be exported to Xl, I
wonder if you would be kind enough to comment on this
approach.

Many thanks again.

Regards.
 
Back
Top