Take a look at application.filesearch in VBA's help.

There's a nice example that you may be able to modify--something like:

Option Explicit
Sub testme()

Dim i As Long

With Application.FileSearch
.LookIn = "C:\My Documents\excel"
.Filename = "*.xls"
.SearchSubFolders = False
If .Execute(SortBy:=msoSortByLastModified, _
SortOrder:=msoSortOrderAscending) 0 Then
MsgBox "There were " & .FoundFiles.Count & _
" file(s) found."
For i = 1 To .FoundFiles.Count
MsgBox .FoundFiles(i)
Next i
Else
MsgBox "There were no files found."
End If
End With

End Sub

If you want to run a macro in the same workbook you could do:

call A1
and
Call A2

(although that looks too much like a cell address for me.)

If that macro is in other workbooks, you can use:

application.run "theotherworkbook1.xls!a1"
and
application.run "theotherworkbook2.xls!a1"



Jac wrote:

Note: the abbreviations are just table names

Following is a little bit more clarification to create a macro that would do
the following:

1. Go to a directory or Drive
Sort all the files in ascending order by size
Open the first file [file will prompt for text delimited mode and open in
excel]
Copy all column A, close the file, and Ok
Open a template [only once] and paste into a specific cell into the template
[ABC.Macro.xls] = VA tab is the default location to paste info in.

2. Data manipulation:
Click on a previously recorded macro [Macro name = A1 ïƒ Note: this will
happen only once or during the first loop].
Click on a previously recorded macro [Macro A2]
Copy highlighted cells from template [sheet] Paste Append into the LE table
of XAB1 database table.
If cell AH12 = TAR, Click on a previously recorded macro [Macro A3], copy
the highlighted section, and Paste Append data into the WE table
Click on a macro button €œMove€� to paste info into another excel tab and hit
Enter. Then, Move the cursor one line below the pasted data on the A column.
Go back to the previous worksheet and click on CleanUp macro.

3. Go back to the directory and move the first file into the Done folder.
4. The loop restarted until 50 files are open and loaded into the database.

Thanks
================================================== ===

"Dave Peterson" wrote:

This?

2. Data manipulation:
Click on a previously recorded macro.
Copy certain cells from template [sheet] into the LE database table. If cell
AH12 = PE, DE, RE, or SE, go to next step; however, if cell AH12 = TAR, click
on TAR macro, select, copy, and paste data into the WE table.
Click on a macro button €œMove€� to paste info into another excel tab and hit
Enter. Then,
Move the cursor one line below the pasted data on the A column.
====

I'm not sure anyone reading this would know what the LE database table is, what
the TAR macro is, or what the WE table is.



Jac wrote:

Data manipulation includes all the steps listed under #2.

Thanks

"Dave Peterson" wrote:

yeah, but...

Until you actually say what this means:
2. Macro will manipulate the data from the file per instructions.

I don't think you'll get much help. And you may be able to record a macro when
you do what ever manipulation you need.

#1. Take a look at application.getopenfilename
You can use that to point at a file

#2. I got nothing.

#3. I don't speak the Access

#4. Post back when you get it working for one workbook and I'm sure someone
will help you with your looping.





Jac wrote:

I think it is much more complex than recording the macro and and combine them
due to the looping process.

Thanks

"Debra Dalgleish" wrote:

To get started, turn on the macro recorder, and record the steps as you
do the process manually.

This will create some code that you can modify.

You can search the Google newsgroup archives for similar questions, that
will provide additional tips and code examples.

http://groups.google.ca/advanced_gro...ugroup=*excel*

If you get stuck modifying a specific part of the code, post a question,
including the relevant code, a description of the workbook, the Excel
version you're using, and the problem you're having.

Jac wrote:
Hi,

Please help me create a macro that does the following:
1. Macro will prompt me to key in a file name from a specific folder.
2. Macro will manipulate the data from the file per instructions.
3. Macro will transfer data to an Access database and will also Prompt as to
which table to upload the data.
4. This process will go into a looping process until the folder [from #1 is
empty].

Your assistance will be greatly appreciated.

Thanks
Jaq


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html



--

Dave Peterson


--

Dave Peterson


--

Dave Peterson