Cannot get past this point:

[Stuart]

From Excel, I create a new mdb (named as the active
excel workbook) and a new table (named as the
activesheet). With the connection open, I call this sub:

Public Sub ImportSpreadsheet(strTable As String, _
strFileName As String, strWorkSheet As String, _
strRange As String)

and then:

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, , strWorkSheet & "!" & strRange
and get the error:
Field 'F1' doesn't exist in the destination table 'Demolition'

What does this error mean, please?

 

[Douglas J. Steele]

You skipped the parameter between strFileName and strWorkSheet & "!" &
strRange in your TransferSpreadsheet statement. That skipped parameter tells
Access whether or not the first row of strWorkSheet & "!" & strRange
contains the names of each field. The default value is False, so Access
assumes the first field in the spreadsheet is named F1, the next one is F2
and so on. It sounds as though table to which you're importing (strTable)
doesn't have fields with those names.

 

[Stuart]

Many thanks. This seems to work:
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
strTable, strFileName, True, strWorkSheet & "!" & strRange
where the target table has Field names, which are the same as the
values in row 1 of the spreadsheet being imported.. Works when I
set the Excel range to include row 1.

However, my last column is importing as '0.00' throughout the last
Field. In other Fields the imported value is correct (ie either text,
a number to 2 decimal places, or blank). In this last Field, every row
is '0.00'. Like all the other numerical Fields, it is set to
General > Field Size > Double
General > Format > Fixed
General > Decimal Places > 2
the Data Type is Number.

What is happening, please?

Regards and thanks.

 

[Douglas J. Steele]

No idea.

Your range doesn't have a hidden column in it, by any chance?

 

[Stuart]

No hidden columns, just a few formulae here and there,
but not only in that last col.
Tried other sheets....same results.
Will try a different workbook and post back.
Regards and thanks.

 

[Joe Fallon]

As I recall , formulae are not allowed.
Copy, Paste Special (Values) to a new worksheet and see if the raw data
imports correctly.

 

[Stuart]

Many thanks.

Immediately before exporting to the mdb, I remove formulae
throughout the sheet, as you suggest. However, the problem
persists.
With that Field set to a default value of '0' in table design mode,
then every row in that Field becomes '0.00' (the data being set to
'number', 'fixed' and two decimal places).
If I set the default value to nothing (ie empty), then the entire
Field is blank/empty. No values at all.

I've only just started using Access, so might it be a very
elementary error of mine?

Regards.

 

[Stuart]

As an update, Icreated a new workbook with one sheet
containing the Field names in row 1, and numerical data
in column 'O'.
I then imported it into my preset Table.

Same result..... row 1 of the Table correctly holds the
Field Names, Field 15 contains '0.00' in every row.

Absolutely dumbflummoxed.

Regards (and Help, please).

 

[John Nurick]

Hi Stuart,

One way of getting the results you describe is to have 15 fields in your
table, and Columns A to O in the worksheet - but to import a range
containing only the first 14 columns. Have you quadruple-checked the
value that strRange contains when you call DoCmd.TransferSpreadsheet?

 

[Stuart]

Thanks, you found it.

For others benefit, (and my embarrassment) here was
the code...
StartRw = 1
EndRw = .Range("N65536").End(xlUp).Row
strRange = "A" & StartRw & ":" & "N" & EndRw

As you spotted, the last column to import is 'O', so
strRange should be:

strRange = "A" & StartRw & ":" & "O" & EndRw

.....why didn't I check what strRange was referencing !!

Regards and thanks