Importing from Excel

  • Thread starter Thread starter Jim Jones
  • Start date Start date
J

Jim Jones

I key the name of an Excel file which I then want to import using the
Transfer Spreadsheet function

So, I use

Dim Myspreadsheet As String
Myspreadsheet = InputBox(“Input name of spreadsheet to be importedâ€)

Then, my problem is how to use the string Myspreadsheet to identify the
Excel file in the TransferSpreadsheet line which follows:

DoCmd.TransferSpreadsheet acImport, , "Mytable", "\\mypath\.............

I need to know how to complete this line using the string MySpreadsheet to
identify the selected Excel file

Any help would be much appreciated.

Jim Jones
Botswana
 
If the user only enters the name of the spreadsheet without the ".xls"
extension, you could do it like this. I will also include some code to
ensure the file actually exists:

Dim strFullPath As String
Dim Myspreadsheet As String

Do While True
Myspreadsheet = InputBox(“Input name of spreadsheet to be importedâ€)
strFullPath = "\\mypath\" & Myspreadsheet & ".xls"
If Dir(strFullPath) = vbNullString Then
If MsgBox("The Spreadsheet " & Myspreadsheet & " Was Not Found",
& _
vbQuestion + vbRetryCancel) = vbCancel Then
Exit Do
End If
Else
DoCmd.TransferSpreadsheet acImport, , "Mytable", strFullPath
Exit Do
End If
 
Jim Jones said:
I key the name of an Excel file which I then want to import using the
Transfer Spreadsheet function

So, I use

Dim Myspreadsheet As String
Myspreadsheet = InputBox(“Input name of spreadsheet to be imported”)

Then, my problem is how to use the string Myspreadsheet to identify the
Excel file in the TransferSpreadsheet line which follows:

DoCmd.TransferSpreadsheet acImport, , "Mytable", "\\mypath\.............

I need to know how to complete this line using the string MySpreadsheet to
identify the selected Excel file

Any help would be much appreciated.

Jim Jones
Botswana
Click to expand...

DoCmd.TransferSpreadsheet acImport, , "Mytable", Myspreadsheet
 
Back
Top