+ Reply to Thread
Results 1 to 4 of 4

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

  1. #1
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    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:

    Please Login or Register  to view this content.
    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

  2. #2
    Forum Guru MarvinP's Avatar
    Join Date
    07-23-2010
    Location
    Woodinville, WA
    MS-Off Ver
    Office 365
    Posts
    16,164

    Re: VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with cod

    Hi rain4u,

    Is the answer SP2?

    http://www.techrepublic.com/forum/questions/101-322285
    One test is worth a thousand opinions.
    Click the * Add Reputation below to say thanks.

  3. #3
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with cod

    Hi Marvin
    I wish I could try something easy as this. But the way our computers are locked out to install anything at all on our computers, I think there will be a bigger chance that I will become the next Loto millionaire than our IT department willing to actually try to improve the software on our desktops. Beside from the same link someone mentioned he had the XP3 installed and he still had the problem. The article claims this will help with a read only issue so I think it might not necessarily resolve the issue ti begin with.

    Any other ideas?


    Cheers
    Rain


    EDIT:
    I approached this problem from adifferent angle and Googled again. And the following caught my eye

    Taken from another forums on another help site


    *Moc.exe is Open XML format converter provided after Office 2007 Compatibility Pack installed.

    The Office 2007 Compatibility Pack will do the conversion job 1st before proceed to open the converted file (in temp folder). If the xlsx file is being open via [Excel 2003 > File > Open], it will be the same issue as via programmatically.
    Please Login or Register  to view this content.
    This actually works in the sense of getting the file open without problem. I tried it and it did open xlsx file on my USB memory stick. But how we could use this to open file via open dialog box so I could choose which file I would like to open.

    I think this could potentially help to resolve the problem and would help anyone in future if one has the same issue .

    Cheers
    Rain
    Last edited by rain4u; 06-22-2011 at 07:51 PM.

  4. #4
    Forum Contributor
    Join Date
    01-29-2011
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    471

    Re: VBA for excel 2003. Open file dialog box, open 2007 file xlsx, continue with cod

    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.



    Please Login or Register  to view this content.
    Any help would be greatly appreciated.
    Cheers
    Rain

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1