Macro Needed in Excel 2007 for opening Files

**tcw1986** · 10-24-2011, 03:30 AM

Hi All,

I'm new to using macros etc and have no experience in how to use VBA but believe this is the way forward for what I'm looking to do (if it is even possible!!)

Essentially, what I want to do, is to have a macro that looks at a particular cell and then opens a file (which is in a specific folder), sorts the data, copies some of the data and then pastes it into a table the original spreadhseet. It then repeats this for a different file in a different folder.
It would then need to do this for several cells within the original spreadsheet.

Can anyone help??

Cheers

**vlady** · 10-24-2011, 04:01 AM

hello this will look for A1 - the file name to be opened.

  
Sub FileOpen()
Dim FileName, Openfile As String

FileName = ActiveWorkbook.Name
Range("a1").Select
FileToOpen = ActiveCell
Workbooks.Open(FileName:=Openfile).RunAutoMacros _
Which:=xlAutoOpen
Windows(FileName).Activate
End Sub

sorts the data, copies some of the data and then pastes it into a table the original spreadhseet

this depends on the opened worksheet what part to be sorted out, etc.

HTH

**royUK** · 10-24-2011, 07:57 AM

To best describe or illustrate your problem you would be better off attaching a dummy workbook, the workbook should contain the same structure and some dummy data of the same type as the type you have in your real workbook - so, if a cell contains numbers & letters in this format abc-123 then that should be reflected in the dummy workbook.

If needed supply a before and after sheet in the workbook so the person helping you can see what you are trying to achieve.

Doing this will ensure you get the result you need!

The easiest way to get started is to record the macro, then edit it.

**tcw1986** · 10-31-2011, 09:46 AM

Hi,

Thanks for your posts.

To help, I've attached some files. The first file (Macro template) is the file I want data pasted into. The second and third files contain the data I need.

I think I will need several macros but I want to be able to look at a cell in the macro template file (in this case cells B8, B47 and B87) which has my "Pack ID" and then open the second file which has the data. The data needs to be sorted in alphabetical order (currently A01, C01, E01 etc etc, needs to be A01, A02, A03 etc). and then paste just the data (column B) into the 'tube sent' column in the original file. It then does the same for the second data file and pastes into the 'tube received' column.

I have no experience of VBA, so I really don't know how to open the area I need to edit the VBA or enter any VBA or anything. Recording a macro at the moment does everything I want but is limited to only opening the file I use while recording the macro, where I want it to open the file specific to B8, B47, B87 etc.

Thanks

Toby