Excell And access 1

[amal1973]

I would like to Access a folder that has 400 excel workbooks. Loop through the folder and accessing a specific sheet. After that I want to Copy selected Cells based on some criteria and then send all theses rows to MS Access.. (All 400 Workbooks are identical in the design, and the sheets name are the same in all workbooks) could this be done?? And if so. Could anyone please tell me where to start?
I have already wrote the excel VBA code for selecting the cells.
Thanks
This is the start of my code
Sub Copy2()
Dim RngDestination As Range
Dim i As Integer
Set RngDestination = Worksheets("Sheet2&quot.Range("A1&quot
For i = Cells(Rows.Count, "M&quot.End(xlUp).Row To 1 Step -1
If Cells(i, "M&quot.Interior.ColorIndex = -4142 Then
Cells((i, "B&quot,(i,"C&quotCells(i,"M&quot).Copy Destination:=RngDestination
Set RngDestination = RngDestination.Offset(RngDestination.Rows.Count)
End If
Next I

But the Rngdestination should not be sheet2,, it should be the access table
Thanks for the tip

 

[danomaniac]

amal1973,

This should get you to open each workbook in a given directory. Change the MyPath and MyFile variables to reflect your directory path.

Sub openfile()

Dim MyFile, MyPath

MyPath = "C:\excel\" 'location of all the workbooks

MyFile = Dir("C:\excel\*.xls&quot 'returns first file with .xls

Do
If MyFile <> &quot;&quot; Then
Workbooks.Open Filename:=MyPath & MyFile

Debug.Print MyFile 'Use this lines to verify your files are being copied then replace it with your action code.

ActiveWindow.Close
Else
Exit Sub
End If

MyFile = Dir ' returns next file with .xls
Loop

End Sub

Hope this helps.

 

[danomaniac]

Sorry, after the debug.print line, I should have said &quot;...verify your files are being opened...&quot; not copied.

 

[amal1973]

Thanks ,your code was what i am looking for ..it worked

 

[Guest_imported]

Hi Amal and others,
I have the same problem. I 'm new with VBA and I want to copy data from excell to access using a submit button. How to proceed.

Thanks for your help guys.

 

[Guest_imported]

Guys:
I 'm a Java programmer who's doing a little bit of work on VBA.

I'm totally new to VBA, so please keep that in mind,if you know the solution to my problem.

The work that I have to do is this:

Our users get lots of excel sheets which they have to get into an access table. I have to write a GUI for them to make it easy for them.

Note:

1. THe excel sheets would have around 6 columns.

2. We need to export the data from excel to access table, but we need only 2 columns.

3. One of the 2 columns has fields that have spaces in between. The spaces should not be there when they go into the access table.

If anybody knows the solution or has code to do this please let me know