Import Excel To Access

[Steve F. via AccessMonster.com]

I'm trying to import an Excel spreadsheet called dh.xls to an Access table
named tblprofilestage. But, I keep getting an error to check the path, but I
know the path is correct.

Can anyone help me with the code? Here it is:

Public Function ImpToAccess()

Dim dbPath As String
Dim gotTable As Boolean

gotTable = False
dbPath = InputBox("Enter Location of Spreadsheet" + Chr(13) + "(drive:\path\)
", "Location of Spreadsheet")
If dbPath <> "" Then
If Dir(dbPath + "dh.xls", vbNormal) <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tblProfileStage", dbPath + "dh.xls", , True
DoCmd.OpenQuery "qryAppendDH", acNormal, acEdit
DoCmd.DeleteObject acTable, "tblProfileStage"
MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
gotTable = True
End If
End If
If gotTable = False Then
MsgBox "No data was imported, please check your path...",
vbCritical, "Import Tables Fail"
ImportExcel = False
Else
MsgBox "Your data has been imported...", vbExclamation, "Import
Successful"
ImportExcel = True
End If


End Function

 

[Douglas J. Steele]

Do you have a slash at the end of dbPath? If not, then dbPath + "dh.xls"
(which really should be dbPath & "dh.xls") won't point to the file.

BTW, rather than make the user type in the folder, why not let them select
it from the Browse for Folder dialogue? Check
http://www.mvps.org/access/api/api0002.htm at "The Access Web" for a
complete example.

 

[Steve F. via AccessMonster.com]

I modified the code, but it doesn't import to Access. I get the message that
the import failed. Here my code:

Public Function ImpToAccess()

Dim dbPath As String
Dim gotTable As Boolean

gotTable = False
dbPath = InputBox("Enter Location of Spreadsheet" + Chr(13) + "(drive:\dbPath\
)", "Location of Spreadsheet")
If "(dbPath\)" <> "" Then
If Dir(dbPath & "dh.xls", vbNormal) <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tblProfileStage", dbPath & "dh.xls", , True
DoCmd.OpenQuery "qryAppendprofile", acNormal, acEdit

MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
gotTable = True
End If
End If
If gotTable = False Then
MsgBox "No data was imported, please check your path...",
vbCritical, "Import Table Failed"
ImportExcel = False
Else
MsgBox "Your data has been imported...", vbExclamation, "Import
Successful"
ImportExcel = True
End If


End Function

Do you have a slash at the end of dbPath? If not, then dbPath + "dh.xls"
(which really should be dbPath & "dh.xls") won't point to the file.

BTW, rather than make the user type in the folder, why not let them select
it from the Browse for Folder dialogue? Check
http://www.mvps.org/access/api/api0002.htm at "The Access Web" for a
complete example.

I'm trying to import an Excel spreadsheet called dh.xls to an Access table
named tblprofilestage. But, I keep getting an error to check the path,

[quoted text clipped - 33 lines]

End Function

 

[Douglas J Steele]

What is

If "(dbPath\)" <> "" Then

supposed to do?

As it stands, it's checking that the literal string (dbPath\) isn't a blank
string, which, of course, it never will be.

If you're trying to check that they actually input something for dbPath, you
need:

If dbPath <> "" Then

If you want to ensure that there's a slash at the end of dbPath, try
something like:

If Right(dbPath, 1) <> "\" Then
dbPath = "\"
End If

or you can simply use

dbPath = Replace(dbPath & "\", "\\", "\")


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I modified the code, but it doesn't import to Access. I get the message that
the import failed. Here my code:

Public Function ImpToAccess()

Dim dbPath As String
Dim gotTable As Boolean

gotTable = False
dbPath = InputBox("Enter Location of Spreadsheet" + Chr(13) + "(drive:\dbPath\
)", "Location of Spreadsheet")
If "(dbPath\)" <> "" Then
If Dir(dbPath & "dh.xls", vbNormal) <> "" Then
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel97,
"tblProfileStage", dbPath & "dh.xls", , True
DoCmd.OpenQuery "qryAppendprofile", acNormal, acEdit

MsgBox "Spreadsheet has been imported...", vbExclamation, "Import Data"
gotTable = True
End If
End If
If gotTable = False Then
MsgBox "No data was imported, please check your path...",
vbCritical, "Import Table Failed"
ImportExcel = False
Else
MsgBox "Your data has been imported...", vbExclamation, "Import
Successful"
ImportExcel = True
End If


End Function

Do you have a slash at the end of dbPath? If not, then dbPath + "dh.xls"
(which really should be dbPath & "dh.xls") won't point to the file.

BTW, rather than make the user type in the folder, why not let them selec t
it from the Browse for Folder dialogue? Check
http://www.mvps.org/access/api/api0002.htm at "The Access Web" for a
complete example.

I'm trying to import an Excel spreadsheet called dh.xls to an Access table
named tblprofilestage. But, I keep getting an error to check the path,

[quoted text clipped - 33 lines]

End Function