asked on

Access 2013 DoCmd.TransferSpreadsheet Not Working in Win 10

Have an Access form I have used for many years. Recently had hard drive crash and was upgraded to Win 10 as part of the restore. Button on the form is supposed to find an Excel file and upload two tabs from it into two tables in Access. First time I have tried it under Win 10 and it does nothing - no import, no error message.

The form has three fields: Year (text), Team (combo box), series # (combo box). Here is the code for the button:

Private Sub cmdImport_Click()

Dim FullPath As String
Dim FileName As String
Dim StatDir As String

FullPath = "C:\Users\User\Documents\DVAL\"
StatDir = "DVAL stats " & txtYear.Value & "\"

FullPath = FullPath & StatDir

FileName = "DVAL" & txtYear.Value & "." & cboTeam.Value & "." & cboSeries.Value & ".xls"
FullPath = FullPath & FileName

DoCmd.SetWarnings True
DoCmd.TransferSpreadsheet acImport, , "hitters", FullPath, True, "hitting$"
DoCmd.TransferSpreadsheet acImport, , "pitchers", FullPath, True, "pitching$"

List0.Requery

End Sub

Rey Obrero (Capricorn1)

test this
the code will stop in the word Stop, to continue line by line hit F8, see if the codes will be executed

Private Sub cmdImport_Click()

Stop  ' added for testing purposes, remove later

 Dim FullPath As String
 Dim FileName As String
 Dim StatDir As String

 FullPath = "C:\Users\User\Documents\DVAL\"
 StatDir = "DVAL stats " & txtYear.Value & "\"

 FullPath = FullPath & StatDir

 FileName = "DVAL" & txtYear.Value & "." & cboTeam.Value & "." & cboSeries.Value & ".xls"
 FullPath = FullPath & FileName

 DoCmd.SetWarnings True
 DoCmd.TransferSpreadsheet acImport, , "hitters", FullPath, True, "hitting$"
 DoCmd.TransferSpreadsheet acImport, , "pitchers", FullPath, True, "pitching$"

 List0.Requery
 End Sub

Open in new window

also check if there is [Event Procedure] click event of the cmdImport button in the Property sheet

.

richard moore

ASKER

I added the Stop line of code as you indicated. Still nothing happens when I click on the button. I tried hitting F8 and still nothing. It's like it's not even starting to execute the code attached to the button.

If I look at the properties of the cmdImport button, for On Click it says [Event Procedure] and if I click the elipsis (...) it brings up the code.

Dale Fye

Check to see whether the connection between the button and the code is still valid.

Open the form in design view, click on the button and view the event properties. Check to see whether there is [Event Procedure] in the Click event of the button. then click on the ellipse (...) at the end of that line and determine whether it takes you to the procedure shown above.

Rey Obrero (Capricorn1)

from vba window DEBUG > Compile
- correct any error raised
- do a Compact and Repair

if problem is not solved
do a decompile - http://www.granite.ab.ca/access/decompile.htm

do a compact and repair

richard moore

ASKER

Dale Fye - yes, clicking the (...) opens the VBA window and takes me to the code the the Click event.

Rey Obrero - In the VBA window, if I click on Debug, the first option is "Compile DVAL2004" but it is grayed out. Not sure why it is called DVAL2004 because the name at the top of the VBA window is DVAL2017. 2004 might be the year I originally wrote this code. In the Project window on the left it says "DVAL2004 (DVAL2017)". The only thing I change every year is the Year field has a default value which gets bumped up by one every year. It is currently set to 2017 but that shouldn't change the name of the Project, right?

richard moore

ASKER

I tried deleting the button, creating a new button and pasting in the same code. The only difference is I now have a "Compile DVAL2004" option on the VBA window. Tried that and it did not give any errors but form still does nothing.

ASKER CERTIFIED SOLUTION

Rey Obrero (Capricorn1)

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

richard moore

ASKER

Well, duh. Never noticed the error at the top of the screen. Wish it had given me an error when I tried to execute code that it was going to ignore. Thanks.