Help with transferspreadsheet

[ondvirg via AccessMonster.com]

I have some code that imports various spreadsheets into a table in my DB. The
2nd column of each spreadsheet is a text value that can be all numbers or U
followed by numbers. The total length of the field is 8 characters. The area
to transfer starts at line 4 which has the column headings that are in the
table.

The problem I'm having is if the first data line (line 5) has the field value
in B5 reflecting all numbers, any subsequent B column values that start with
"U" don't populate that field. The data line itself is loaded to the table,
but that field is left blank. I think it has something to do with the
transfer function thinking this is a number field and when it comes to one
begining with a character, it just ignores the field even though it loads the
record?

This is the code I'm using, I hope someone can shed some light on why the
field is being left blank?
Thanks.


If (MsgBox("This will add data from ALL files. Are you sure you want to
continue? Press OK to continue, or CANCEL to abort.", 305) = 1) Then

DoCmd.OpenForm "FRM_Wait"
DoCmd.RepaintObject

Dim strPathFile As String, strFile As String, strPath As String, strArea As
String
Dim strTable As String
Dim strBatchID As String
Dim strTypeID As String
Dim blnHasFieldNames As Boolean


blnHasFieldNames = True
strPath = [MainDirectory] & [SubDirectory2]
strTable = "TBL_Data"
strArea = "A4254"
strFile = Dir(strPath & "*127.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strBatchID = Mid$(strFile, 9, 3)
strTypeID = Mid$(strFile, 8, 1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7, strTable,
strPathFile, blnHasFieldNames, strArea

DoCmd.SetWarnings False
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "UPDATE [" & strTable & "] SET BatchID = '" &
strBatchID & "' WHERE BatchID IS NULL"
strSQL2 = "UPDATE [" & strTable & "] SET TypeID = '" & strTypeID
& "' WHERE TypeID IS NULL"

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

strFile = Dir()
Loop
End If

 

[GBA]

create an Import Specification with a name

then use your TransferSpreadSheet command with the named Import
Specification parameter

 

[Ken Snell MVP]

See this article:
Avoid DataType Mismatch Errors when Importing Data from an EXCEL File or
when Linking to an EXCEL File
http://www.accessmvp.com/KDSnell/EXCEL_ImpExp.htm#DataTypeErr

 

[Ken Snell MVP]

Import Specifications cannot be used with TransferSpreadsheet.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

create an Import Specification with a name

then use your TransferSpreadSheet command with the named Import
Specification parameter

I have some code that imports various spreadsheets into a table in my DB.
The
2nd column of each spreadsheet is a text value that can be all numbers or
U
followed by numbers. The total length of the field is 8 characters. The
area
to transfer starts at line 4 which has the column headings that are in
the
table.

The problem I'm having is if the first data line (line 5) has the field
value
in B5 reflecting all numbers, any subsequent B column values that start
with
"U" don't populate that field. The data line itself is loaded to the
table,
but that field is left blank. I think it has something to do with the
transfer function thinking this is a number field and when it comes to
one
begining with a character, it just ignores the field even though it loads
the
record?

This is the code I'm using, I hope someone can shed some light on why the
field is being left blank?
Thanks.


If (MsgBox("This will add data from ALL files. Are you sure you want to
continue? Press OK to continue, or CANCEL to abort.", 305) = 1) Then

DoCmd.OpenForm "FRM_Wait"
DoCmd.RepaintObject

Dim strPathFile As String, strFile As String, strPath As String, strArea
As
String
Dim strTable As String
Dim strBatchID As String
Dim strTypeID As String
Dim blnHasFieldNames As Boolean


blnHasFieldNames = True
strPath = [MainDirectory] & [SubDirectory2]
strTable = "TBL_Data"
strArea = "A4254"
strFile = Dir(strPath & "*127.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strBatchID = Mid$(strFile, 9, 3)
strTypeID = Mid$(strFile, 8, 1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
strTable,
strPathFile, blnHasFieldNames, strArea

DoCmd.SetWarnings False
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "UPDATE [" & strTable & "] SET BatchID = '" &
strBatchID & "' WHERE BatchID IS NULL"
strSQL2 = "UPDATE [" & strTable & "] SET TypeID = '" &
strTypeID
& "' WHERE TypeID IS NULL"

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

strFile = Dir()
Loop
End If

 

[GBA]

well live & learn

can one use the TransferText with a spreadsheet as the source?

Import Specifications cannot be used with TransferSpreadsheet.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

create an Import Specification with a name

then use your TransferSpreadSheet command with the named Import
Specification parameter

I have some code that imports various spreadsheets into a table in my DB.
The
2nd column of each spreadsheet is a text value that can be all numbers or
U
followed by numbers. The total length of the field is 8 characters. The
area
to transfer starts at line 4 which has the column headings that are in
the
table.

The problem I'm having is if the first data line (line 5) has the field
value
in B5 reflecting all numbers, any subsequent B column values that start
with
"U" don't populate that field. The data line itself is loaded to the
table,
but that field is left blank. I think it has something to do with the
transfer function thinking this is a number field and when it comes to
one
begining with a character, it just ignores the field even though it loads
the
record?

This is the code I'm using, I hope someone can shed some light on why the
field is being left blank?
Thanks.


If (MsgBox("This will add data from ALL files. Are you sure you want to
continue? Press OK to continue, or CANCEL to abort.", 305) = 1) Then

DoCmd.OpenForm "FRM_Wait"
DoCmd.RepaintObject

Dim strPathFile As String, strFile As String, strPath As String, strArea
As
String
Dim strTable As String
Dim strBatchID As String
Dim strTypeID As String
Dim blnHasFieldNames As Boolean


blnHasFieldNames = True
strPath = [MainDirectory] & [SubDirectory2]
strTable = "TBL_Data"
strArea = "A4254"
strFile = Dir(strPath & "*127.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strBatchID = Mid$(strFile, 9, 3)
strTypeID = Mid$(strFile, 8, 1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
strTable,
strPathFile, blnHasFieldNames, strArea

DoCmd.SetWarnings False
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "UPDATE [" & strTable & "] SET BatchID = '" &
strBatchID & "' WHERE BatchID IS NULL"
strSQL2 = "UPDATE [" & strTable & "] SET TypeID = '" &
strTypeID
& "' WHERE TypeID IS NULL"

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

strFile = Dir()
Loop
End If

 

[Ken Snell MVP]

TransferText will only let you import files whose extensions are .txt.,
..csv, .tab, or .asc, so the answer is no. You could save the workbook file
as a .csv file and then import it.
--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

well live & learn

can one use the TransferText with a spreadsheet as the source?

Import Specifications cannot be used with TransferSpreadsheet.

--

Ken Snell
<MS ACCESS MVP>
http://www.accessmvp.com/KDSnell/

create an Import Specification with a name

then use your TransferSpreadSheet command with the named Import
Specification parameter



:

I have some code that imports various spreadsheets into a table in my
DB.
The
2nd column of each spreadsheet is a text value that can be all numbers
or
U
followed by numbers. The total length of the field is 8 characters.
The
area
to transfer starts at line 4 which has the column headings that are in
the
table.

The problem I'm having is if the first data line (line 5) has the
field
value
in B5 reflecting all numbers, any subsequent B column values that
start
with
"U" don't populate that field. The data line itself is loaded to the
table,
but that field is left blank. I think it has something to do with the
transfer function thinking this is a number field and when it comes to
one
begining with a character, it just ignores the field even though it
loads
the
record?

This is the code I'm using, I hope someone can shed some light on why
the
field is being left blank?
Thanks.


If (MsgBox("This will add data from ALL files. Are you sure you want
to
continue? Press OK to continue, or CANCEL to abort.", 305) = 1) Then

DoCmd.OpenForm "FRM_Wait"
DoCmd.RepaintObject

Dim strPathFile As String, strFile As String, strPath As String,
strArea
As
String
Dim strTable As String
Dim strBatchID As String
Dim strTypeID As String
Dim blnHasFieldNames As Boolean


blnHasFieldNames = True
strPath = [MainDirectory] & [SubDirectory2]
strTable = "TBL_Data"
strArea = "A4254"
strFile = Dir(strPath & "*127.xls")

Do While Len(strFile) > 0
strPathFile = strPath & strFile
strBatchID = Mid$(strFile, 9, 3)
strTypeID = Mid$(strFile, 8, 1)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel7,
strTable,
strPathFile, blnHasFieldNames, strArea

DoCmd.SetWarnings False
Dim strSQL1 As String
Dim strSQL2 As String

strSQL1 = "UPDATE [" & strTable & "] SET BatchID = '" &
strBatchID & "' WHERE BatchID IS NULL"
strSQL2 = "UPDATE [" & strTable & "] SET TypeID = '" &
strTypeID
& "' WHERE TypeID IS NULL"

DoCmd.RunSQL strSQL1
DoCmd.RunSQL strSQL2
DoCmd.SetWarnings True

strFile = Dir()
Loop
End If