Macro

[thesuggs76]

I get sent 5 seperate excel sheets from different teams on a monthly basis. I then normally copy and past them into one overall monthly sheet.
Is there a Macro that can automatically do that, any help appreciated

 

[GTO]

Yes.

Are you trying to learn how to write the code, or have someone write it for you?

Mark

 

[Jon von der Heyden]

It probably is possible.

If you save all the monthly files into a dedicated directory folder for the specific month then we could probably build a macro for you to loop through the directory and colsolidate the files. But first you need to be more specific please.

Is it just one sheet in each file that you are consolidating? What is the sheet name?

When you say you paste them into an overall monthly sheet, is that one sheet per month?

What ranges are occupied in each of the monthly files (.e.g columns A:Z with column labels in row1)?

 

[thesuggs76]

Hi
Yes there are 5 seperate spreadsheets, but only one worksheet in each of them. Yes i basically copy all the data into one overall sheet that contains the data from all 5.
The range is from A:H with column labels in 1

Thanks

 

[Jon von der Heyden]

You could use something like the below. Please take the time to read the comments as you need to adapt this.

To install the reference, in the VBE go Tools > References and place a check mark beside the MS ActiveX Object Library.

CODE
-----------------------------------------------------------------------
<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Explicit</SPAN><br> <br><SPAN style="color:#00007F">Sub</SPAN> ConsolidateText()<br><br><SPAN style="color:#007F00">'< COMMENTS ></SPAN><br>    <SPAN style="color:#007F00">'// references Micrsoft ActiveX Data Objects 2.8 Library //</SPAN><br>    <SPAN style="color:#007F00">'// assumes all files are contained in a single directory folder //</SPAN><br>    <SPAN style="color:#007F00">'// assumes that the folder exclusively holds the files for consolidation //</SPAN><br>    <SPAN style="color:#007F00">'// (1) change to point to your directory folder //</SPAN><br>    <SPAN style="color:#007F00">'// (2) data will paste to the active sheet starting from column A, change to suit //</SPAN><br>    <SPAN style="color:#007F00">'// (3) assumed that the sheet in each file is called 'Sheet1', change to suit //</SPAN><br><SPAN style="color:#007F00">'< /COMMENTS ></SPAN><br> <br><SPAN style="color:#00007F">Dim</SPAN> recData <SPAN style="color:#00007F">As</SPAN> ADODB.Recordset<br><SPAN style="color:#00007F">Dim</SPAN> strConnection <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strSQL <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strFileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, strPath <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br><SPAN style="color:#00007F">Dim</SPAN> rngCell <SPAN style="color:#00007F">As</SPAN> Range<br> <br><SPAN style="color:#00007F">Set</SPAN> recData = <SPAN style="color:#00007F">New</SPAN> ADODB.Recordset<br> <br>strPath = "C:\Excel\Files\Text" <SPAN style="color:#007F00">' ### (1)</SPAN><br>strFileName = Dir(strPath & "\*.xls*")<br> <br><SPAN style="color:#00007F">Do</SPAN> <SPAN style="color:#00007F">While</SPAN> Len(strFileName) > 0<br>    <SPAN style="color:#00007F">Set</SPAN> rngCell = Range("A" & Rows.Count).End(xlUp).Offset(1) <SPAN style="color:#007F00">' ### (2)</SPAN><br>    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _<br>        "Data Source =" & strFileName & "; Extended Properties=Excel 8.0"<br>    strSQL = "SELECT * FROM [Sheet1$]" <SPAN style="color:#007F00">' ### (3)</SPAN><br>    <SPAN style="color:#00007F">Call</SPAN> recData.Open(strSQL, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdText)<br>    <SPAN style="color:#00007F">Call</SPAN> rngCell.CopyFromRecordset(recData)<br>    recData.Close<br>    strFileName = Dir<br><SPAN style="color:#00007F">Loop</SPAN><br><br><SPAN style="color:#00007F">Set</SPAN> recData = <SPAN style="color:#00007F">Nothing</SPAN><br> <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[thesuggs76]

Thanks for that. I just need a little more help. You can call me think, but i am hopeless when it comes to VBA.
I made the change to the References as mentioned.
Where does the code go, at the moment i click on Tools>macros>Visual Basic Editor

I changed the code as you mentioned, ie changed the directory, i left the other 2 as they both start from column A and are named Sheet1.
Should i then assign this to a command button, click on it and hey presto i look like a genius. At the moment I'm feeling more dense than genius.

 

[Jon von der Heyden]

Once you are in the VBE go to the menu bar and click Insert > Module.
Paste the code into the big white code pane.

Back in the worksheet that you want to collect the data in hit ALT+F8 and run the macro called 'ConsolidateText' from the dialog.

 

[thesuggs76]

Thanks for all the help, i think i'm nearly there.
I've got it to work, but then when i try to do it again, it stops working and points to this line as the problem

Call recData.Open(strSQL, strConnection, adOpenForwardOnly, adLockReadOnly, adCmdText)

Any suggestions.

I have referenced Micrsoft ActiveX Data Objects 2.8 Library as you told me

Thanks

 

[Jon von der Heyden]

Do you get any particular error message?

 

[thesuggs76]

The error message i get is

Run-time error '-2147217865(80040e37)':
The microsoft jet database engine could not find the object 'Sheet1$. Make sure the object exists and that you spell its name and the path correctly