Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
Record Macro Relative does not work? | Excel Discussion (Misc queries) | |||
excel macro inconsistency | Excel Discussion (Misc queries) | |||
Macro and If Statement | Excel Discussion (Misc queries) | |||
macro interruption: help!!! | Excel Discussion (Misc queries) |