Hi folks
At work we have excel 2003. Every time when I open some 2007 files with extensions .xlsm or xlsx for a split second it shows a little window and says its converting the file or something and it has cancel option. So we have Excel 2003 and I suppose the machine has the support package to convert the files.
Now I need to have a macro that is capable to open up a 2007 file with extension xlsx and xlsm (also regular xls file as well please) and then carry on with the remaining code (concatenate some columns, index/match lookup from the file where the macro was launched).
At the moment I have the following code which should allow me to open a xlsx and write in some text to header row:

Option Explicit
 Sub testme()
Dim Finfo As String
 Dim FilterIndex As Integer
 Dim Title As String
 Dim FileName As Variant
 ' Set up list of file filters
 Finfo = "Workbook (*.xls),*.xls," & "Office 2007 Excel Workbook (*.xlsx),*.xlsx,"
 ' Display *.* by default
 FilterIndex = 1
 ' Set the dialog box caption
 Title = "Select a File to Import"
 ' Get the filename
FileName = Application.GetOpenFilename(MultiSelect:=True)
 ' Handle return info from dialog box
 If FileName = False Then
 MsgBox "No file was selected."
 Else
 'MsgBox "You selected " & FileName
 ' opens an the excel file at the location: FileName
 Workbooks.Open (FileName)
 End If
 
 
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "test"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "test"
 End Sub
If I use this code on regular xls it works. But as soon as I try to use .xlsx it prompts me with error:

"this file is not in recognizable format."

If you know the file is is from another program which is incompatible with Microsoft Office Excel, click Cancel, then open this file in its original application.
If you suspect the file is damaged, click Help for more information about solving the problem.
If you still want to see what text is contained in the file, click OK. Then click Finish in the Text Import Wizard.

When clicking OK it does what it says. Its all text and its all gibberish.


To make things even more harder the file that I would try to open will always have a dynamic name so it can be different name every single occasion. I guess I could change it to something specific if there is no way around it. Some of the files may prompt that it has macros so do you want to enable the macros (Macro security is on medium setting).

Is there any solution to my problem? I have tried bunch of different stuff and also Googled about the subject with no luck.

Any help would be immensely appreciated.
Cheers
Rain