TransferSpreadsheet works on some pcs but not on others.

[bob almady]

I am using MS Access 2000. I have an application that Imports different spreadsheets from a MS Excel 2000 workbook that exists on the network.

When I run the application on 2 of the pcs, the transferspreadsheet works - every time.

When I run the application on 2 other pcs, the transferspreadsheet does not work - every time.

The input file is always the same. The table that I'm importing into is always the same, and already exists.

I have researched the possibility that MS Access on the various pcs are not at the same version (or possibly MS Excel). But have had no luck finding any differences. Help!

[willakawill]

I am using MS Access 2000. I have an application that Imports different spreadsheets from a MS Excel 2000 workbook that exists on the network.

When I run the application on 2 of the pcs, the transferspreadsheet works - every time.

When I run the application on 2 other pcs, the transferspreadsheet does not work - every time.

The input file is always the same. The table that I'm importing into is always the same, and already exists.

I have researched the possibility that MS Access on the various pcs are not at the same version (or possibly MS Excel). But have had no luck finding any differences. Help!

Hi
This sounds like a network problem and possibly user permissions on your network

[bob almady]

Thanks for the response. However, I am the network administrator, and I have run the application logged onto all of the pcs as the network administrator, with the same results. It works on some pcs but not others.

[willakawill]

Thanks for the response. However, I am the network administrator, and I have run the application logged onto all of the pcs as the network administrator, with the same results. It works on some pcs but not others.

Great. Can you post code please?

[bob almady]

Th input spreadsheet has 14 tabs, Mon Days, Mon Nights, Tues Days, Tues Nights, weds......, thurs, fri, sat, sun
The Path is always the same and the FileName is always the same(for a given week)..
The day of the week is selected and the 2 spreadsheets for that day are imported into MS Access using the Transferspreadsheet. Row 6 on each spreadsheet contains the headings of the fields.

Here is the guts of the code.

 
Public Function ImportFromElginProdRptXLS(PathString, ProdRprtFileName)
Dim DayName As String
Dim DayNameCounter As Integer
Dim ProdRptPathAndFileName As String
'
' CALLED FROM IMPORTSCREEN
'
ProdRptPathAndFileName = PathString & "\" & ProdRprtFileName
 
DayNameCounter = Form_ImportScreenElgin.DayofWeekListBox.ListIndex
If DayNameCounter = -1 Then
   ImportFromProdRptXLS = DayNameCounter
   Exit Function
End If
 
DayName = Form_ImportScreenElgin.DayofWeekListBox.ItemData(DayNameCounter)
On Error GoTo TransferSpreadsheetDay_failed
If DayNameCounter = 0 Then
 
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "ProductionReportXLS", ProdRptPathAndFileName, True, "MON-DAYS!A6:AU150"
   On Error GoTo TransferSpreadsheetNight_failed
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
     "ProductionReportXLS", ProdRptPathAndFileName, True, "MON-NIGHTS!A6:AU150"
End If
If DayNameCounter = 1 Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "ProductionReportXLS", ProdRptPathAndFileName, True, "TUES-DAYS!A6:AU150"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "ProductionReportXLS", ProdRptPathAndFileName, True, "TUES-NIGHTS!A6:AU150"
End If
If DayNameCounter = 2 Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "ProductionReportXLS", ProdRptPathAndFileName, True, "WEN-DAYS!A6:AU150"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
    "ProductionReportXLS", ProdRptPathAndFileName, True, "WEN-NIGHTS!A6:AU150"
End If
.... for each day ....
 
 

[willakawill]

The first thing that pops into my head is how the pcs are setup for date and time. If this works on the same network on one pc and not on another there are usual suspects and this is prime. Users, as you know, sometimes change settings that cause problems like this.

[Killer42]

Don't know whether this will help or not, but can we get some detail on exactly how it "doesn't work"? What happens on the "bad" PCs?

[bob almady]

I was able to solve the problem! The gory details follow.

The Transferspreadsheet was not producing any error messages because I had suppressed warning messages using

DoCmd.SetWarnings False
 

Commenting that command out allowed me to get messages. Though the messages were not very helpful.

I finally just tried to import the spreadsheet manually within the MS Access application, and then the messages started to have some value.

Through much research I was able to discover that 3 of the 46 columns in the Excel file that I was importing were defined with General format although the data in the fields was numeric, and the fields in the MS Access table had them defined as Number 0 Decimal. (It had been working fine for a year on 2 pcs like this, but not on 2 other pcs).

I nevertheless modified the format of the columns in the Excel file to be Numeric, hoping that would solve it. By itself, this did not solve the problem. It turns out that I also apparently had to refresh the receiving table in the MS Access application (I made no changes to it). Until I did that, the change of format in MS Excel did not make a difference.

[willakawill]

I was able to solve the problem! The gory details follow.

The Transferspreadsheet was not producing any error messages because I had suppressed warning messages using

DoCmd.SetWarnings False
 

Commenting that command out allowed me to get messages. Though the messages were not very helpful.

I finally just tried to import the spreadsheet manually within the MS Access application, and then the messages started to have some value.

Through much research I was able to discover that 3 of the 46 columns in the Excel file that I was importing were defined with General format although the data in the fields was numeric, and the fields in the MS Access table had them defined as Number 0 Decimal. (It had been working fine for a year on 2 pcs like this, but not on 2 other pcs).

I nevertheless modified the format of the columns in the Excel file to be Numeric, hoping that would solve it. By itself, this did not solve the problem. It turns out that I also apparently had to refresh the receiving table in the MS Access application (I made no changes to it). Until I did that, the change of format in MS Excel did not make a difference.

Nasty bug. Congratulations