how to search in a directory path for excel files and copy to acce

[Guest]

Dear All,

Please how can I programmatically search in a directory path for excel files
and copy to access table. I have several excel files in a directory, I want
to write a vb code that will search through the directory and get excel files
one after another, open them and copy the worksheets to access table. I
already have working codes use TransferSpreadsheet method to copy worksheets
from a single workbook. I want to search through the directory for more excel
files. That is the problem.

Any help is appreciated
Gokop

 

[Guest]

Assuming you know the directory path, you can use the Dir function to loop
through a directory. VBA Help has a good example of how to do that.

If you need the user to be able to first select the directory, here is a
site that has some code that will allow the user to browse for a folder:

http://www.mvps.org/access/api/api0002.htm

 

[Guest]

Dear Klatuu,

Thanks for that. I am using system that does not have the msdn installed to
get help. Any idea on the Dir function code manipulation?

Kind regards
Gokp

 

[Guest]

Are you saying you don't have the help file that is part of Access VBA?

Here is a simple example:

Dim strPath As String
Dim strFileName As String
Dim strFullPath as String

strPath = "C:\Documents and Settings\Frodo\My Documents\Spreadsheets\"

Or if you use the BrowseFolder:

strPath = BrowseFolder("Select Folder")
If strPath = vbNullString Then
MsgBox "No Folder Selected"
Else
strPath = strPath & "\"

strFileName = Dir(strPath & "*.xls")
Do While strFileName <> vbNullString

strFullPath = strPath & strFileName
Docmd.TransferSpreadsheet .......
Dir()
Loop
End If

First, you pass the Dir function a beginning path and and mask for the files
you want to retrieve. In the example above, you want to select all xls files
in the directory.

Dir returns only the file name, so you need to put the path and the filename
together for the TransferSpreadsheet.

When there are no more files that match, Dir returns an empty string.

 

[Guest]

Dear Klatuu,

Thanks very much. I think this will lead me to the solution.

Regards
Gokop

 

[Guest]

Dear Klatuu,

Thanks very much. It works find.

I also added a query that adds two columns. It works find. I want the first
column to be filled with the Workbook name, that is the file name of the
excel and the second column to be filled with the worksheet name. I am using
INSERT INTO-- VALUES statement. It does not work. Please any idea? See the
code below:

Private Sub Command7_Click()
Dim xlApp As Excel.Application
Dim xlWS As Excel.Worksheet
Dim xlWB As Excel.Workbook
Dim i As Integer
Dim strFileName As String


Dim wkShName As String
Dim strFolderPath As String
Dim strPath As String
Dim strPathBrowser As String
Dim bookName As String
Dim strFileNameValue As String
Dim strFullPath As String
Dim j As Integer

Set xlApp = New Excel.Application
On Error Resume Next
strPath = "C:\Documents and Settings\a99858\My Documents\"
strFileName = Dir(strPath & "*.xls")

strFullPath = strPath & strFileName
Do While Len(strFileName) > 0


strFullPath = strPath & strFileName
strFileNameValue = strFileName

xlApp.Workbooks.Open (strFullPath)

For j = 1 To xlApp.Worksheets.count
Set xlWS = xlApp.ActiveWorkbook.Worksheets(j)
wkShName = xlWS.Name

DoCmd.TransferSpreadsheet acImport, , "MultiSheet_Example", strFullPath, -1,
wkShName & "!A1:F8"

DoCmd.RunSQL "ALTER TABLE MultiSheet_Example ADD COLUMN CCCode CHAR, GCode
CHAR", -1
DoCmd.RunSQL "INSERT INTO MultiSheet_Example (CCCode ,GCode) VALUES (&
strFileNameValue, & wkShName)"

Next j

strFileName = Dir()
Loop

End Sub

Please any idea on how to insert the filename without the .xls extension in
the first column of the access table and the worksheet name in the second
column.

Let me explain a little bit. I am working on an cost accounting document of
a company. The workbook is named after the cost center name eg Sales and the
Worksheet is named after the general ledger code (GLCode eg EE00875). After
trasfering all worksheets of all workbooks into one access table, the only
distinguishing value is the Workbookname and worksheet names. I will use this
to query general ledgers and create Profit and Lost Account for each cost
centre.

Sorry for the lengnthy explanation.

Any help is appreciated
Gokop