Importing Data from Excel: Please help.

[pedie]

Hi,I need help with VBA to import Data from Excel into Acccess. I have excel templete where data is being field out and then different users from thier own end [fill out this required data and append it to access table.]

Now what i want to do it, to have a button in a form and then when click on i want dailog box to pop up and let users select the templete excel workbook and then when selected i want the data from sheet1 [particular] range to be imported to this access table1...

Is this possible?
Please make it work for me...


Thanks in advance everyone!


Pedie

 

[dk]

Pedie

Please read the posting guidelines <a href="http://www.mrexcel.com/forum/showthread.php?t=127080">here</a> if you haven't already and please pay particular attention to points 5,6,7 and 8. As you have already realised people on this forum are more than willing to help but there is normally an expectation that you will make some effort to solve the problem yourself. I am not trying to be mean but with 3000+ posts you should be getting this by now.

For this particular question you first need to work out how to display a File Open dialog box in Access. A quick Google search of this site found <a href="http://www.mrexcel.com/forum/showthread.php?t=82469">this post</a>.

You then need to import the chosen file. If it's just a specific range then you should be able to create an import specification and then use Docmd.TransferSpreadsheet to get the data in.

Please have a go at trying to do this yourself - if/when you get stuck then please post back with the problems you're having

DK

 

[Trevor G]

Hi Pedie,

In your database have you looked at using the DoCmd.TransferSpreadsheet command?

You can supliment the workbook name with the Excel Dialogue box option.

Here is sample code to open the dialog box

Sub cmdFileDialog_Click()

' Requires reference to Microsoft Office 11.0 Object Library.

Dim fDialog As Office.FileDialog
Dim varFile As Variant


' Set up the File Dialog.
Set fDialog = Application.FileDialog(msoFileDialogFilePicker)

With fDialog

' Allow user to make multiple selections in dialog box
.AllowMultiSelect = True

' Set the title of the dialog box.
.Title = "Please select one or more files"

' Clear out the current filters, and add our own.
.Filters.Clear
.Filters.Add "Excel Workbooks", "*.XLS"

.Filters.Add "All Files", "*.*"

' Show the dialog box. If the .Show method returns True, the
' user picked at least one file. If the .Show method returns
' False, the user clicked Cancel.
If .Show = True Then

'You can add the DoCmd Transfer here
Else
MsgBox "You clicked Cancel in the file dialog box."
End If
End With
End Sub

 

[pedie]

Thanks Trevor, thanks DK for the link and for reminding me the forum regulations... the thing is that 'm really new to access and just dont know what to look for sometime...even if i look dont know how does it work....

From your suggestions i tried the following code which errored...

External table is not the expected format - 3274 runtime error..

went though this link but i dont think i can make this work myself...

[/FONT]
[FONT=Courier New]DoCmd.TransferSpreadsheet acImport, 3, "MyTable", "C:\Users\Pediez\Desktop\Book1.xlsm", True, "A2:a10"[/FONT]
[FONT=Courier New]

 

[pedie]

Trevor, from the code you've provided how can i get the selected file name?

Thanks again.

 

[pedie]

Thanks again....this link helps...

Thanks again guys for helping!!!

 

[Trevor G]

Sorry Pedie,

I have been away from the PC most of today, I hope you have a working solution.

Enjoy the weekend....

 

[pedie]

Hi Trevor, that okay and yes the problem is solved now....

You have a great weekend too!