Excel Data and Access Forms

[Tracyice]

I have a portion of the data that I want to enter in my access form already in an excel spreadsheet. Is there a way to have the fields on the access form automatically populates by the data in excel?

 

[KenReay]

Hi

You can link to an Excel spreadsheet as if it were an access table, see file\getexternaldata\link on menu

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[randy700]

How about...
Link to the spreadsheet (File|Get External Data|Link Tables) and base your form on it.


Randy

 

[Tracyice]

I have tried this. The problem is, for each record, the data will be coming from a different excel file. In addition, not all of the data that needs to be entered in the form, is in the excel spreadhseet.

 

[randy700]

When the data you need on a form comes from multiple locations (tables), create a query to base the form on. Have you considered linking to the spreadsheet in code (using the TransferSpreadsheet method) and importing the necessary data to an access table?


Randy

 

[Tracyice]

I have never used the TransferSpreadsheet method in any of my code, I am reviewing the syntax now from the msdn site. I think this may give me what I need. Thank you.

 

[Tracyice]

I have a field on my form called txtEnterFile. I want the user to enter the filename they wish to import to excel here. This works with the code below, however, it will only accept a file name from one area on my computer, namely, My Documents. It will not recognize a file from any other folders in my Documents. This database will be stored on a shared drive, so I need the function to work for any file on the drive. Any suggestions?


Private Sub CmdImportTable_Click()
Dim txtEnterFile As String
txtPrompt = txtFileName

DoCmd.TransferSpreadsheet transfertype:=acImport, _
tablename:=txtEnterFile, _
FileName:=txtEnterFile, Hasfieldnames:=True, _
Range:="Sheet1!B2:B5"
End Sub