Transferspreadsheet confusion

[Dave]

I understand that there is a transferspreadsheet command that I can
use to import certain cells of an excel file into a certain table of
my database. I will have the code assigned to a button.

There will be, however, several spreadsheets (same spreadsheet
structure, different patient) to pick from. How can I get user input
to select which spreadsheet file to import with the
transferspreadsheet command?

Thanks,

Dave B

 

[Pieter Wijnen]

You'll find a File Open Dialog Function at http://www.mvps.org/Access under
the API section

HTH

Pieter

 

[Ken Snell \(MVP\)]

Pieter's suggestion will let your user select the EXCEL file.

But you then will need to write VBA code that actually opens the EXCEL file,
gets a list of the spreadsheet names that are in that file, closes the file,
presents the list of spreadsheet names to the user (I find that a listbox is
the best control to use), and then the user would select the desired sheet
name so that your VBA code could pass the name to the TransferSpreadsheet
action.
--

Ken Snell
<MS ACCESS MVP>



"Pieter Wijnen"

 

[fredg]

I understand that there is a transferspreadsheet command that I can
use to import certain cells of an excel file into a certain table of
my database. I will have the code assigned to a button.

There will be, however, several spreadsheets (same spreadsheet
structure, different patient) to pick from. How can I get user input
to select which spreadsheet file to import with the
transferspreadsheet command?

Thanks,

Dave B

Here is a simple method:

Dim strSheet as String

strSheet = MsgBox("Enter the full path to the spreadsheet, and the
Spreadsheet name")
If(Right(strSheet,4) = ".xls" Then
Else
strSheet = strSheet & ".xls"
End If

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourTableName",strSheet

You can add the "Has Field Names" and the "Range" arguments to the
above code.

The complete path would look something like:
"c:\FolderName\MySpreadsheetName.xls"

 

[Pieter Wijnen]

inputbox might work better <g>

Pieter

Here is a simple method:

Dim strSheet as String

strSheet = MsgBox("Enter the full path to the spreadsheet, and the
Spreadsheet name")
If(Right(strSheet,4) = ".xls" Then
Else
strSheet = strSheet & ".xls"
End If

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9,
"YourTableName",strSheet

You can add the "Has Field Names" and the "Range" arguments to the
above code.

The complete path would look something like:
"c:\FolderName\MySpreadsheetName.xls"

 

[fredg]

inputbox might work better <g>

Pieter

Blush! Blush! I goofed in my post.... but not in my thought. <g>

Thanks for calling this to our attention.