'File - Open'

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm using the following code to import data into a table. It works fine at
the moment but I would like to alter it to be able to select from a 'File
Open' dialogue box.

Just to make it a little more challenging can it default to the location
"G:\06_ip\Rainchecks\"?

DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"Tbl_Raincheck_Data_With_Comments", _
"G:\06_ip\Rainchecks\March\Rainchecks All Data Thu 300306 - Sun
020406.xls", _
True, _
"Data_With_Comments"
 
Hi,

I've copied everything into a module but I continually get an error similar
to 'Only Comments may appear after........"

I've tried a few things to sort it out but still isn't working. I'm using
Access 2000 and Windows XP but I suspect that doesn't make any difference.

Are you able to look at the code on the site and see what I need to do?
 
Open a new module, and copy everything that's between Code Start and Code
End to that new module.

Save the module, making sure you don't name it the same as any routines in
the module.

In the routine that's doing the TransferSpreadsheet, you'd use code like:

Dim strFilter As String
Dim strInputFileName as string

strFilter = ahtAddFilterItem(strFilter, "Excel Files (*.XLS)", "*.XLS")
strInputFileName = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Please select an input file...", _
InitialDir := "G:\06_ip\Rainchecks\", _
Flags:=ahtOFN_HIDEREADONLY)

If Len(strInputFileName) > 0 Then
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, _
"Tbl_Raincheck_Data_With_Comments", _
strInputFile, _
True, _
"Data_With_Comments"
End If
 
Thanks Douglas - that worked. Previously I was doing it as part of the code
on the button I was using. I didn't realise there was a difference.
 
Back
Top