My Macro no longer works

[Jan Il]

Hi - Access 2002, WinME

Two years ago Stve Schapel helped me create the function below to
automatically update a table in my SDG&E electrical meter database for data
downloaded from a website to my hard drive.

I just reinstalled 2002 back in October 2003, and then the website went
down. It is back on line now, and for some reason I am getting a debug error
when I try to run this function. The code below in behind a button on the
control form. Nothing has changed since the last time I downloaded the last
updates, except for the reinstall of v2002. The part of the code below where
the debugger throws the error is this line (which is all on one line in the
database.

.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Download",
"C:\Excel\Download\Download.xls", True

My question is, at the time he helped with this code I had Access 2000. At
that time he said that the number 19 in this line of code,
acSpreadsheetTypeExcel9, should represent Excel 2000. In that case, should
that number be changed to the number that represents
Excel 2002? I am not sure if this is the cause of the problem, and I am only
taking a guess at this. The download process no longer works as it did
before, and in view of the fact that the code had been working perfectly up
until today, and the only change made to any part of the process being the
change in program versions, I just thought I would check this out first to
eliminate the most obvious before going into extensive troubleshooting.

I would truly appreciate it if someone would review the code and see if
indeed the version number should be changed, and advise me as to what it
should be changed to.

Very best regards,

Jan

*******************Start of Code*******************************
Private Sub Command79_Click()
With DoCmd
.SetWarnings False
.OpenQuery "qryDeleteDownload"
.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "Download",
"C:\Excel\Download\Download.xls", True
.OpenQuery "append to monthly billing download"
.SetWarnings True
End With

MsgBox "Download Complete" 'add other arguments as desired
sExit:
Exit Sub
sError:
MsgBox "Error in Download Process"
Resume sExit

End Sub
**********************End of Code*****************

 

[Steve Schapel]

Hi Jan,

Long time no see

I think your diagnosis is correct, though at this stage I am not 100%
sure. Access 2002 allows different syntax in the TransferSpreadsheet
method. By the way, it is not 19 you are looking at the l on the end of
Excel as a 1. It is just 9, but Access 2002 recognises a Excel 8-10
version option, for which the "code number" is 8, but we're not really
going backwards! Anyway, try it like this, and let us know...
.TransferSpreadsheet acImport, 8, "Download",
"C:\Excel\Download\Download.xls", True

 

[Jan Il]

Hi Steve!

Hi Jan,

Long time no see

Yeah, it has been a while. See....if you didn't do such good work I'd have
to come back more often. ;-))

I think your diagnosis is correct, though at this stage I am not 100%
sure. Access 2002 allows different syntax in the TransferSpreadsheet
method. By the way, it is not 19 you are looking at the l on the end of
Excel as a 1. It is just 9, but Access 2002 recognises a Excel 8-10
version option, for which the "code number" is 8, but we're not really
going backwards! Anyway, try it like this, and let us know...
.TransferSpreadsheet acImport, 8, "Download",
"C:\Excel\Download\Download.xls", True

'k...I now see the twix on the Excel l and 9. Tends to help with the other
pair of eyes in place too. <g>

I've tried it with the new code and...Voila!! It's now back up to snuff!
;-)) This is really one of my favorite pieces of code. The function is so
slick and saves so much time I sure didn't want to lose it. Curious why it
won't work with the 9 in view of what you say about the 8-10 version option,
but, it must have to do with the different syntax with 2002 as you said.
However, I /never/ argue with good results, nor an Access MVP. <vbg>

Thank you again for your time and help, I truly appreciate it.

Best regards,
Jan