Import Spreadsheet Into Access - Issue

[Lrn2Code]

Hi,

I've been searching to see if I can find a solution to the problem I'm having - so far I haven't so will take a chance and post my question.

I'm trying to import Excel spreadsheets into an Access database and I keep getting an error on the DoCmd line where it's creating the spreadsheet in the db. It's telling me the path isn't found and the path the error lists isn't even the path I have created to run. Is there some default path the computer always uses? My code follows -

Public Sub import_Floppy()

On Error GoTo err_Import_Floppy

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim rstSchool As DAO.Recordset
Dim qdf As DAO.QueryDef
Dim strSQL As String
Dim strMsg As String
Dim strSch As String
Dim strDelete As String
Dim strUpdate As String
Dim strSD As String
Dim intSch As Long
Dim strSU As String
Dim varAns As Variant
Dim intCnt As Integer
Dim strDistIn As String
Dim strToImp As Variant 'CHANGED FROM STRING TO VARIANT 1-9-09
intCnt = 0
Dim strFileName As String
strFileName = Dir("d:\EdCensus09Export\") 'CHANGED A:\ TO D:\ 10-29-08
'get district id from disk
Select Case Left(strFileName, 1)
Case "T", "J", "U"
strDistIn = Left(strFileName, 4)
If strDistIn = "U022" Then strDistIn = Left(strFileName, 5)

Case "S"
strDistIn = Left(strFileName, 5)

Case Else
MsgBox ("District ID does not appear to be valid. Please check the disk.")

End Select
strDistIn = strDistIn & "_" 'CHANGED A:\ TO D:\ 10-29-08 'REMOVED "d:\" & FROM BEGINNING OF THIS STATEMENT AFTER = 1-9-09


Set dbs = CurrentDb
Dim rstCheck4diSt As DAO.Recordset

''for loading data into a raw file folder on network
Dim strSUFldr As String
Dim strDISTFldr As String
Dim strID As String
Dim dDate As Date
Dim dTime As VbDateTimeFormat

''use to avoid error message when on subsequent SU disks
Dim intSULevel As Integer
intSULevel = 0

DoCmd.Hourglass False

''check that disk contains 6 files
Do Until strFileName = ""
intCnt = intCnt + 1
strFileName = Dir
Loop

If intCnt > 6 Then
strMsg = "There are more than 6 files on this disk. Import will not continue."
MsgBox (strMsg)
Exit Sub
ElseIf intCnt < 6 Then
strMsg = "There are less than 6 files on this disk. Import will not continue."
MsgBox (strMsg)
Exit Sub
End If

'Dim strSQL As String

strSQL = "delete * from exp_c_suemporg"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

strSQL = "delete * from exp_tblposrole"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

strSQL = "delete * from exp_tblschoolclasses"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

strSQL = "delete * from exp_tblteachercourses"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

strSQL = "delete * from exp_tblteacherinfo"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

strSQL = "delete * from exp_tblteamteachers"
Set qdf = dbs.CreateQueryDef("", strSQL)
qdf.Execute

'ADDED CODING TO INCLUDE DATE AND TIME OF DATA LOAD ON FILES

strToImp = strDistIn & "exp_c_suemporg.xls" & "_" & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & "_" & Hour(Now()) & ":" & Minute(Now()) & ":" & Second(Now())

Debug.Print (strToImp)
' COMMENTING OUT TO TRY ABOVE CODE INSTEAD 1-9-09 "(" & CStr(Month(dDate)) & "-" & CStr(Day(dDate)) & "-" & CStr(Year(dDate)) & "_" & CStr(Time(dTime)) & ")"

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strToImp, True

For some reason whenever this code tries to run it stops on the red line above and I get a Microsoft Jet Engine error that the path name isn't found and it lists a C:\Documents and Settings\My Documents (blah blah blah) path. I can't find anywhere in the code where that path is listed. How can I figure out where that path is coming from so I can change it?

Thanks muchly for your guidance!

 

[HiTechCoach]

The problem looks like that you are never setting the folder.

Where are you setting the correct folder path?

What is strDistIn?

 

[Lrn2Code]

strDistin sets the ID number for the district that the spreadsheet is from. Every single district/school/supervisory union will have spreadsheets related to them and that's how each is differentiated - by their number.

Okay so if the path is missing where should it be listed and how should the strToImp variable reflect that?

 

[HiTechCoach]

You have:

strToImp = strDistIn & "exp_c_suemporg.xls" & "_" & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & "_" & Hour(Now()) & ":" & Minute(Now()) & ":" & Second(Now())

Is this the name of a file you are importing? How will an exisitng fiel be named the current date/time.

? "exp_c_suemporg.xls" & "_" & Month(Now()) & "-" & Day(Now()) & "-" & Year(Now()) & "_" & Hour(Now()) & ":" & Minute(Now()) & ":" & Second(Now())
exp_c_suemporg.xls_1-9-2009_15:19:35

This does not look like is is correct.


To include the correct folder, you will need something like:

strToImp = "folder oath here\" & strDistIn & ...

It looks more like you are building a unique file name to export into, not an existing file name to import form.

Has this code actually worked before?

 

[Lrn2Code]

To my knowledge this code has worked before. This is the first time I'm working with this data loader.

There are 6 different files that are being imported from a disk into the "backend" data. Those file names are as follows (with the first 4 characters differing depending on which organization they relate to) -

T043_exp_c_suemporg
T043_exp_tblposrole
T043_exp_tblschoolclasses
T043_exp_tblteachercourses
T043_exp_tblteacherinfo
T043_exp_tblTeamTeachers

I was asked to include the date and time of import on the file load because there are times when organizations update their data and resubmit it. We need to make sure we're working from the most current version of their data. There will not be an existing field named for date, time etc. That is supposed to be included in the file name upon import. Maybe I misunderstood the code I found online to add the date/time to a file name.

I'm going to see if I can figure out how the "folder oath here \" coding will work. I think I need to "change directory" perhaps and then put that string in where you listed the "folder oath here\".

Please let me know if I'm missing something or totally misunderstanding this. Thank you for your guidance!

 

[gemma-the-husky]

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97, strToImp, True

your parameters are wrong here

should be a tablename BEFORE the strtoimp filename

the tablename to which you want to add the imported records

--------
so in fact you are imprting to the strtoimp tablename which may be illegal, and you are imprting the path "true", whcih is probably being resolved as the default mydocs path

 

[Lrn2Code]

Okay...I'll have to change the structure because the strToImp has the table name in it. Thank you!

 

[gemma-the-husky]

the correct syntax is

docmd.transferspreadsheet transfermode, spreadsheettype, tablename, pathname etc

you dont have separate arguments for tablename AND pathname, (so i assume you have a comma embedded in the pathname you are using to separate the tablename and pathname) - so i am not sure whether this is the same or not - i suspect not - you ought to use two different variables, one for the tablename and one for the pathname i think

 

[Lrn2Code]

Okay...I'll give that a try. Thank you!