Importing from Excel

[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

 

[Klatuu]

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

 

[Stuart McCall]

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

DoCmd.TransferSpreadsheet acImport, , "Mytable", Myspreadsheet

 

[Jim Jones]

Klatuu

Many thanks - worked first time!

Jim Jones

 

[Jim Jones]

Many thanks

Jim Jones

DoCmd.TransferSpreadsheet acImport, , "Mytable", Myspreadsheet