Macro Creation
I need to create a macro that does all of the following in order;
I open a new workbook and start my macro which 1. goes to a specific folder and opens all of the files in there (excell spreadsheets) 2. for each file I need the macro to highlight all of column g and do a "text to column" with the output being input into the workbook I opened at the start with the output from each individual file being inserted under the output from the last one. Is this possible? Thanks |
Macro Creation
Martina,
Why don't you do it once with the macro recorder on ... it will store precisely all your steps ... HTH Carim |
Macro Creation
Well, basically the point behind this is, i need to open all of the files
that are in a folder, and this needs to be done at the end of each month (it is to create monthly reports). But there could be 2 files in there at the end of one month and then at the end of the next month their might be 20. "Carim" wrote: Martina, Why don't you do it once with the macro recorder on ... it will store precisely all your steps ... HTH Carim |
Macro Creation
Martina,
Then you should try in the object "In this Workbook" an automatic open such as Private Sub Workbook_Open() Workbooks.Open("D:\My Documents\Excel\Data Files.xls") End Sub HTH Carim |
Macro Creation
Option Explicit
Sub GetData() Dim rw As Long Dim sfile As String Dim wb As Workbook ' book being opend Dim ws As Worksheet Dim wsResult As Worksheet Dim wbResult As Workbook Dim root As String Dim source As Range rw = 1 root = "C:\temp\*.xls" sfile = Dir(root) If sfile = "" Then MsgBox "Nofiles found!" Else Set wbResult = Workbooks.Add(xlWBATWorksheet) Set wsResult = wbResult.ActiveSheet Do Set wb = Workbooks.Open(sfile) Set ws = wb.ActiveSheet With ws Set source = .Range(.Range("G1"), .Range("G1").End(xlDown)) End With With wsResult .Cells(rw, 1).Resize(source.Rows.Count, 1).Value = source.Value End With rw = rw + source.Rows.Count wb.Close False sfile = Dir() Loop Until sfile = "" MsgBox "Done" End If ' now add the code to text to column for the resulting column A End Sub Patrick Molloy Microsoft Excel MVP "MartinaL" wrote: I need to create a macro that does all of the following in order; I open a new workbook and start my macro which 1. goes to a specific folder and opens all of the files in there (excell spreadsheets) 2. for each file I need the macro to highlight all of column g and do a "text to column" with the output being input into the workbook I opened at the start with the output from each individual file being inserted under the output from the last one. Is this possible? Thanks |
All times are GMT +1. The time now is 08:45 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com