docmd.TransferSpreadsheet odd results

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello once again,
I am using a simple docmd.TransferSpreadsheet...... to import an excel
spreadsheet into a table.
It seems to work perfectly, as long as I don't have the excel doc open when
executing the Access VBA Code.

Is this because the Excel doc is open?
Or more specifically because I have the excel doc open and I am also using
Access to excute the VBA code?


Is there a way to see if the Excel doc is open before executing the
docmd.TransferSpreadsheet.... ?
 
Just use error handling to trap the error, e.g.:

Err_Handler:
Select Case Err.Number
Case 3010
MsgBox "Unable to create the query." & vbCrLf & _
"If Excel is already open, close it, and try again.",
vbExclamation, "Export failed."
Case Else
'... whatever00
End Select
Resume Exit_Handler
 
Mr. Browne,
Thank you for your quick response.

I'm getting an append warning box that it could not append 1 field
regardless of the excel doc open or not. This is the column header of column
16 in Excel. The format for F16 is date, so this is expected. If the excel
doc is open, I get an append warning box that it could not append

Upon further testing, I have narrowed the problem. Not sure how code to
include, but here are the steps I'm taking and the results.

Spreadsheet has 16 rows of data. Row 1 is column title, which is deleted
later in my code.

WORKS: run the transfer spreadsheet code with Excel doc not opened. Get
warning box of 1 field not added. All data transfers correctly, except field
f16 on Row 1 (column title on row 1 in column 16). f16 is a date format. So
this is expected.

DOESN’T WORK: when running the transfer spreadsheet, IF the EXCEL doc chosen
is already open, it takes about 5 times longer to import. I get a warning
box that says "fields in 16 records were deleted...â€, this is data in Column
16 (f16 in my table) on 1st tab. F16 is a date column, with the column name
being row 1.

I click ok to the warning message, and it completes the import except Column
16.

I go back to excel, close the excel doc and then get a message box that
appears to be from Excel that notifies me the excel document (name from user
selection above) is now available for editing. If I click OK, it opens an
instance of Excel, but then freezes Excel. Access operates fine.

I thought if I could identify if the Excel doc is open before the transfer
spreadsheet command, I could just avoid all of this....
or Can I open the excel doc as READ ONLY in the transfer spreadsheet command?

Any thoughts would be greatly appreciated.

Here is the code up to the transfer spreadsheet line.

‘**** CODE SAMPLE
Private Sub cmd_Import_MTT_Click()
10 On Error GoTo cmd_Import_MTT_Click_Error

Dim strFilter As String
Dim strInputFileName As String
Dim tempCount As Integer
Dim Response As String

'this will prompt user to select excel file to import
20 strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)",
"*.XLS")
30 strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
Flags:=ahtOFN_HIDEREADONLY)

40 Me.txt_InputFile = strInputFileName
47 DoCmd.OpenForm "frm_Processing"

48 Forms!frm_Processing.Refresh
49 Forms!frm_Processing.Repaint


50 DoCmd.SetWarnings False

'These tables should already be blank, but just in case...
60 DoCmd.OpenQuery "qry_Clear_temp_Excel_Action_Info"
70 DoCmd.OpenQuery "qry_Clear_temp_Excel_Action_Info_Row"
80 DoCmd.OpenQuery "qry_Clear_temp_Excel_Action_Info-0"
90 DoCmd.OpenQuery "qry_Clear_temp_Excel_Step_Info"
100 DoCmd.OpenQuery "qry_Clear_temp_Excel_Step_Info_Row"
110 DoCmd.OpenQuery "qry_Clear_temp_Excel_Step_Info-0"
120 DoCmd.OpenQuery "qry_Clear_temp_Excel_Requirement_Info"
130 DoCmd.OpenQuery "qry_Clear_temp_Excel_Requirement_Info_Row"
140 DoCmd.OpenQuery "qry_Clear_temp_Excel_Requirement_Info-0"
150 DoCmd.OpenQuery "qry_Clear_temp_tbl_FR_Static"
160 DoCmd.OpenQuery "qry_Clear_temp_tbl_IA_Assigned_to_FR"
170 DoCmd.OpenQuery "qry_Clear_temp_tbl_TR"
171 DoCmd.OpenQuery "qry_Clear_tbl_TD_Upload_Requirements"
172 DoCmd.OpenQuery "qry_Clear_tbl_TD_Upload_Test_Lab"
173 DoCmd.OpenQuery "qry_Clear_tbl_TD_Upload_Test_Plan"

174 DoCmd.SetWarnings True 'temporary for troubleshooting
'MsgBox "Before Transfer"

'add rows from selected spreadsheet Requirement Info tab to
temp_Excel_Requirement_Info-0 table

180 DoCmd.TransferSpreadsheet acImport, ,
"temp_Excel_Requirement_Info-0", strInputFileName, False, "Req & BTS Info$A:Z"


‘many queries below to manipulate the data in the
temp_Excel_Requirement_Info-0 table.

‘***** CODE SAMPLE END *******
 
What error number are you getting?
And what does the error message say?

Excel data is untyped, and JET is quite poor at recognising the data types
and performing the import consistently. So, whenever I have needed to import
from Excel, I import into a temporary table that has all text fields, and
then manipulate the data before appending to the real tables. That means
running a bunch of checks for things like:
- correct data type
- range check,
- look for duplicates in a Unique field,
- missing data for a required field,
- values that don't match if it is destined for a foreign key field,
and so on.

I can't tell if any of those action queries are actually trying to alter the
data in Excel (e.g. if you have attached the spreadsheet.) The import using
TransferSpreadsheet should not open a visible instance of Excel.
 
Mr. Browne,
Thank you again for your response. no error message, just the warning boxes
from the append queries. I also import from excel into a temp file. I
modified the temp table from date to text and it imported the numeric value
of the date. However, when I append this data from the temp table (text
field type) to the REAL table (date field type), I received a warning box
notification about not being able to add the fields becuase of data type
violation.

How can I copy the numeric date value (text field type) in my temp_table to
the date value (date field type) in my real_table?
 
That's one of the tests you need to run against the temp table before import
to the real table.

If the value might in from Excel as a number such as 38870, use CVDate().
If it comes in as text, use IsDate() to test if the value can be interpreted
as a date.
 
Back
Top