VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with code

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

I found the following code from the Internet (see below). I have no clue if this could be used together with GetOpenFilename. I wonder if it would be possible to use GetOpenFilename to find the xlsx you want to open, and somehow define files name and full path as FullPathAndFileName and use this code to ope the file.

Maybe someone smarter person could look into this. So far I haven't found anything on the Internet that would allow to use getopenfilename and open xlsx files. If some one can crack this tough nut, I bet there would at least few people who might like this macro.

' Headers/declarations
 
 Option Explicit
 Dim ExcelApp As Object
 Dim ExcelWorkbook As Object
 
 Private Declare Function OpenProcess Lib "kernel32" _
 (ByVal dwDesiredAccess As Long, ByVal bInheritHandle As Long, _
 ByVal dwProcessId As Long) As Long
 
 Private Declare Function GetExitCodeProcess Lib "kernel32" _
 (ByVal hProcess As Long, lpExitCode As Long) As Long
 
 Private Const STATUS_PENDING = &H103&
 Private Const PROCESS_QUERY_INFORMATION = &H400
 
 '
 
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 '
 ' Process entire Excel file and extract plots from charts and worksheets
 '
 ' This can be done from a machine running VB with Excel 2003
 ' that will read Excel 2007 files (.xlsx).
 '
 ' It uses the Microsoft Moc.exe utility (which is installed when the MS file
 ' converter is installed) to convert and open up the .xlsx *without* the
 ' pesky popup.
 '
 ' Use at your own risk.
 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
 Sub ProcessExcelFile(sFullPathAndFileName As String)
 
 On Error GoTo ehandler:
 
 Dim ExcelApp As Object
 Dim ExcelWorkbook As Object
 Dim ICnt As Integer
 Dim Start As Single
 Dim i As Integer
 Dim j As Integer
 Dim sType As String
 
 Dim Worksheet_Count As Integer
 
 ' PNG, GIF, JPG
 sType = "PNG"
 
 Start = Timer
 
 Set ExcelApp = CreateObject("Excel.Application")
 
 ' Note in using Excel 2003 files:
 ' To open workbook *directly* (if you knew the file was an Excel 2003 format)
 ' instead of using the ShellAndWait
 '
 ' Set ExcelWorkbook = ExcelApp.Workbooks.Open(sFullPathAndFileName)
 '
 ' You would convert things like ExcelApp.Activeworkbook.ActiveSheet.Name to
 ' ExcelWorkbook.ActiveSheet.Name
 '
 ' You can't seem to use the ShellandWait method, because Excel will open the file, but ShellAndWait will
 ' only continue *after* the Excel application is shut down!
 '
 
 
 ' this opens a workbook (Excel 2007)
 Call ShellandWait("C:\Program Files\Microsoft Office\Office12\Moc.exe " & sFullPathAndFileName)
 
 
 Debug.Print ExcelApp.ActiveWorkbook.Name
 
 ExcelApp.Visible = False
 
 
    ' Begin the loop through all CHARTS in the workbook
    For i = 1 To ExcelApp.ActiveWorkbook.Charts.Count
    
       ' Select the chart
       ExcelApp.ActiveWorkbook.Charts(i).Select
       
 '     Debug.Print "--->" & ExcelApp.ActiveWorkbook.ActiveSheet.Name
 '     Debug.Print "--->" & ExcelApp.ActiveWorkbook.ActiveSheet.ChartObjects.Count
       
        ' single chart
        ' Extract the *entire* chart as a single JPG
        ExcelApp.ActiveWorkbook.ActiveChart.Export FileName:=CurDir & "\output\" & _
            ExcelApp.ActiveWorkbook.ActiveChart.Name & "." & sType, FilterName:=sType
       
       ' Extract individual charts, if they exist
       For j = 1 To ExcelApp.ActiveWorkbook.ActiveSheet.ChartObjects.Count
       
       ExcelApp.ActiveWorkbook.ActiveSheet.ChartObjects(j).Chart.Export FileName:=CurDir & "\output\" & _
            ExcelApp.ActiveWorkbook.ActiveChart.Name & _
           "_plot" & Format(j, "000") & "." & sType, FilterName:=sType
       
       Next j
       
    Next i
    
    ' Begin the loop through all WORKSHEETS in the workbook
    For i = 1 To ExcelApp.ActiveWorkbook.Worksheets.Count
    
       ' Select the chart
       ExcelApp.ActiveWorkbook.Worksheets(i).Select
       
 '     Debug.Print "--->" & ExcelApp.ActiveWorkbook.ActiveSheet.Name
 '     Debug.Print "--->" & ExcelApp.ActiveWorkbook.ActiveSheet.ChartObjects.Count
    
       ' Extract individual charts, if they exist
       For j = 1 To ExcelApp.ActiveSheet.ChartObjects.Count
       
       ExcelApp.ActiveWorkbook.ActiveSheet.ChartObjects(j).Chart.Export FileName:=CurDir & "\output\" & _
            ExcelApp.ActiveWorkbook.ActiveSheet.Name & _
           "_plot" & Format(j, "000") & "." & sType, FilterName:=sType
       
       Next j
       
    Next i
         
 
 ExcelApp.ActiveWorkbook.Close SaveChanges:=False
 
 ExcelApp.Quit
 
 MsgBox "Done; it took " & Timer - Start & " seconds."
 
 Exit Sub
 
ehandler:
 
 MsgBox "Error = " & Error
 
 End Sub
 
 
 
 
 Private Sub cmdProcessExcel_Click()
 
 Call ProcessExcelFile("c:\test\test_03.xlsx")
 
 
 End Sub
 
 Public Function ShellandWait(ExeFullPath As String, _
 Optional TimeOutValue As Long = 0) As Boolean
    
    Dim lInst As Long
    Dim lStart As Long
    Dim lTimeToQuit As Long
    Dim sExeName As String
    Dim lProcessId As Long
    Dim lExitCode As Long
    Dim bPastMidnight As Boolean
    
    On Error GoTo ErrorHandler
 
    lStart = CLng(Timer)
    sExeName = ExeFullPath
 
    'Deal with timeout being reset at Midnight
    If TimeOutValue > 0 Then
        If lStart + TimeOutValue < 86400 Then
            lTimeToQuit = lStart + TimeOutValue
        Else
            lTimeToQuit = (lStart - 86400) + TimeOutValue
            bPastMidnight = True
        End If
    End If
 
    lInst = Shell(sExeName, vbMinimizedNoFocus)
    
 lProcessId = OpenProcess(PROCESS_QUERY_INFORMATION, False, lInst)
 
    Do
        Call GetExitCodeProcess(lProcessId, lExitCode)
        DoEvents
        If TimeOutValue And Timer > lTimeToQuit Then
            If bPastMidnight Then
                 If Timer < lStart Then Exit Do
            Else
                 Exit Do
            End If
    End If
    Loop While lExitCode = STATUS_PENDING
    
    ShellandWait = True
    
ErrorHandler:
 ShellandWait = False
 Exit Function
 End Function

Any help would be greatly appreciated.
Cheers
Rain