#1   Report Post  
Jac
 
Posts: n/a
Default Macro - - Automation

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
  #2   Report Post  
Debra Dalgleish
 
Posts: n/a
Default

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

  #3   Report Post  
Jac
 
Posts: n/a
Default

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


  #4   Report Post  
Jac
 
Posts: n/a
Default

This should clarify everything:
Need to create a macro that would do the following into a loop:
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 templage
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.
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.


"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

  #5   Report Post  
Dave Peterson
 
Posts: n/a
Default

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


  #6   Report Post  
Jac
 
Posts: n/a
Default

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

  #7   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
  #8   Report Post  
Jac
 
Posts: n/a
Default

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

  #9   Report Post  
Dave Peterson
 
Posts: n/a
Default

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
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 3 December 13th 04 09:43 PM
Record Macro Relative does not work? lbbss Excel Discussion (Misc queries) 1 December 13th 04 08:55 PM
excel macro inconsistency JM Excel Discussion (Misc queries) 2 December 9th 04 02:13 AM
Macro and If Statement SATB Excel Discussion (Misc queries) 2 December 3rd 04 05:46 PM
macro interruption: help!!! mario milani Excel Discussion (Misc queries) 1 November 30th 04 07:02 PM


All times are GMT +1. The time now is 04:35 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright 2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"