Hi all,
I have an interesting (well I think interesting) little problem. I have a bunch of files (~30) from an experiment saved in a pseudo-CSV format containing two columns of data and a bit of junk at the start and end. What I need to do is compile it all into a single Excel worksheet with the columns side by side for graphing & analysis. Here’s a snippet of a file:
Thu Oct 16 16:00:08 2003
SCAN
Wavelength (nm)
Abs
1
510
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
Yes, the hard-returns at the start are in the files too. Everything up to ‘510’ is unnecessary. It ends with a bit of junk too, but I can probably take that out by hand. Actually the stuff at the start I can take out too, the hard part is getting it all side by side.
So, anyone have any ideas? In another thread Rob managed to get them all into a single text file quite easily, but they were one after the other, does that help at all?
Many thanks in advance,
discobiscuit
There are sevral possible ways to do this. Don’t get me wrong I love applescript 
, but I’ve been doing some VBA scripting (Excel macro) lately and I’ve already done what you need. This might be an easier solution for you particular problem. Insert this into a macro in excel.
This will allow you to select several excel files at once and put your data all into one spreadsheet.
You will need to edit the code a little so that it selects your data correctly.
Sub test_Macro()
'
'
' Load data files
'
'
'
Dim varFilenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim strTest As String
Dim wSht As Worksheet
Dim allwShts As Sheets
' Get the files, may select more than one file
varFilenames = Application.GetOpenFilename("Excel Files (*.xls), *.xls", , "Select the data files. Hold the Ctrl- key to select more than one file.", , True)
'Create workbook
Workbooks.Add
ActiveSheet.Name = "Data"
strActiveBook = ActiveWorkbook.Name
On Error GoTo 0
'Loop through the workbooks to open
counter = 1
' ubound determines how many items in the array
Application.ScreenUpdating = False
While counter <= UBound(varFilenames)
'Opens the selected files
Application.StatusBar = "Opening " & varFilenames(counter)
Workbooks.Open varFilenames(counter), , ReadOnly
strSourceDataFile = ActiveWorkbook.Name
'set range in Excel format -"A1"
'THIS IS THE PART YOU WILL NEED TO EDIT TO CORRECTLY MOVE YOUR DATA
'This calculates the destination
myRange = GetExcelColumn((counter - 1) * 2 + 1) & "1"
Workbooks(strSourceDataFile).Sheets(1).Range("A1:B30").Copy _
Destination:=Workbooks(strActiveBook).Sheets("Data").Range(myRange)
' Close the data workbooks
Application.DisplayAlerts = False
Workbooks(strSourceDataFile).Close
Application.DisplayAlerts = True
'increment counter
counter = counter + 1
Wend
Application.StatusBar = False
Application.ScreenUpdating = True
End Sub
Private Function GetExcelColumn(ByVal iCellNo As Integer) As String
Dim iBeg, iEnd As Integer
' If 1-26, then this is an easy conversion
If iCellNo < 27 Then
GetExcelColumn = Chr$(iCellNo + 64)
Else
' Now we have to account for AA-ZZ
iBeg = iCellNo 26 ' Get the first letter
iEnd = iCellNo Mod 26 ' Get the second letter
If iEnd = 0 Then
' If iEnd is 0, then it is Z, which should be 26
iEnd = 26
'** you need to subtract 1 from the initial letter otherwise your lettering will be the next letter in the alphabet
iBeg = iBeg - 1
End If
GetExcelColumn = Chr$(iBeg + 64) & Chr$(iEnd + 64)
End If
End Function
Thanks scott997. I’ve tried making this into a macro but to be honest, I haven’t got the faintest idea how Excel macros actually work. I saved it in my ‘Personal Macros Workbook’ but when I try to run it (with the relevant files open) I just get a compile error : Syntax error. When the debugger loads it highlights the very first line! 
Not doing very well am I. It’s probably something simple, sorry to bother you with it. I’ll keep trying…
discobiscuit
Update: I managed to fix that by taking out all the tabs and so forth, it seems to like it better that way. Now when I run it I get the error:
Run-time error '1004':
Method 'GetOpenFilename' of object '_Application' failed
Any ideas? Like I said I have no idea how all this stuff works, sorry to bother you with most probably trivial stuff.
It is trivial. Microsoft sucks.:evil: I’ve been running the macro at work on a Windows machine. :oops: I figured it should work without any problems on Excel regardless of the platform.:evil: I was wrong. There was a syntax error that I needed to fix. But it still will not do what you need because the GetOpenFilename has a Microsoft bug that will not allow you to select more than one file. This cascades into a larger problem because the filenames are no longer in an array…
If you can bring yourself to use Windows to do it, this should work. Otherwise it should be easy to use applescript to tell excel to open each file one at a time, copy the data and paste it into your desired spreadsheet . I know this is not as helpful as providing code. Sorry. If you are still having problems, post here and I’ll see what I can do. I’m off for vacation for the next couple of days though.
Scott
discobiscuit,
I was thinking about this and I was wondering what output you were trying to get. Is the desired result something like this:
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
284.577, -0.00281326
285.394, -0.00337415
286.212, -0.00186142
287.029, -0.00303439
With all 30 pairs of data in two columns. Or are you looking for this:
284.577, -0.00281326, 284.577, -0.00281326 ……
285.394, -0.00337415, 284.577, -0.00281326 ……
286.212, -0.00186142, 284.577, -0.00281326 ……
287.029, -0.00303439, 284.577, -0.00281326 ……
With 60 columns of data. Are the original files in text format?
Andy
Thanks again Scott, MS does indeed suck. As you suggested ended up writing an Applescript to copy and paste it all. Andy; I’m looking for a modified version of the second situation. Modified since I don’t actually need the first column from all the files; it remains constant.
Anyway, what I did was to use Rob’s text compiling script from another thread to get all the data in one file then open it in Excel. Then I use a second script to select the relevant data and paste it all side by side. It took me a while to write out all the values for 35+ data sets, but now that it’s done I don’t need to do it again.
Thanks again for your help.
discobiscuit