+ Reply to Thread
Results 1 to 3 of 3

Hidden file extensions

  1. #1
    Registered User
    Join Date
    04-02-2004
    Posts
    16

    Hidden file extensions

    Hi,

    I have a macro that opens ASCII data in Excel by first obtaining channels names and units from a text file and then opening an ASCII file to import that actual data. The important thing to note is that all the files have the same name (which varies according to each car that is tested) but just have different extensions; the file name below, 'Testdata', is just an example. It works like this:

    Use GetOpenFilename to get the name of the text file: Testdata.txt
    Open Testdata.txt
    Copy data of interest (in row wise format) and paste a transpose of it (so that it's column wise) in empty space. Delete the original data.
    Create a new filename variable based on the return from the above GetOpenFilename that has a .asc extension, instead .txt (by just trimming the extension off and adding the new one)
    Open Testdata.asc
    Copy the data of interest
    Activate the Testdata.txt window
    Paste the data into that window
    Close Testdata.asc
    Save Testdata as an xlsx file.

    This has worked fine for a long time, with only the occasional hiccup, which I've only just had the chance to trace. It seems that if you choose to keep Windows file extensions hidden then the macro fails at the point where it should copy the data from Testdata.asc. I personally never hide file extensions so it's always worked on my machine. However, I tried to find a solution to the problem by hiding file extensions and inserting new pieces of code but I just keep running into a brick wall. Here's what I've found out so far, after hiding file extensions:

    When Testdata.asc is opened it is NOT the foremost window and so when I try to copy the data I want from that file I end up copying data from Testdata.txt, which stays as the foremost window.

    I inserted an extra line to activate the Testdata.asc window but the code Windows(Dir(FileToOpen)).Activate doesn't work, even though it does work when file extensions are not hidden.

    I ran a short macro to list what windows where open after breaking the macro when Testdata.txt and Testdata.asc were open. It listed Testdata.txt and Testdata.asc so Excel recognises that both file types are open at the same time in individual windows.

    I swapped any occurrence of Windows(Dir(FileToOpen)).Activate (for the text file) or Windows(Dir(FileToOpen2)).Activate (for the ASCII file) for Windows("Testdata.txt").Activate or Windows("Testdata.asc").Activate as relevant and this failed with a subscript out of range error. So, it seems that even though Excel is well aware of the exact names of the open files, it will not let me access them using the activate windows method when including extensions.

    So: ideally I need a simple solution that ensures that Testdata.asc is the active window when the copying process takes place and Testdata.txt is the active window when the pasting process takes place.

    Many thanks in anticipation.

    Simon.

  2. #2
    Registered User
    Join Date
    12-17-2016
    Location
    South Wales
    MS-Off Ver
    Excel 2007, 2016
    Posts
    68

    Re: Hidden file extensions

    No guarantee that this will work! But is an option to unhide extensions at the start of the code, and revert to the original status at the end? If so, a google search implies that this may work:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    04-02-2004
    Posts
    16

    Re: Hidden file extensions

    Thanks for this. I've incorporated something similar to the above and it works perfectly.

    Regards,

    Simon.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Renaming file extensions
    By sparx in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-15-2016, 06:39 PM
  2. [SOLVED] File Extensions
    By 1vision in forum Excel General
    Replies: 4
    Last Post: 05-19-2014, 04:35 PM
  3. Changing File Extensions.
    By Faith Wheeler in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-12-2013, 02:45 PM
  4. File Open with two extensions
    By c.vaibhav in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-05-2011, 03:36 AM
  5. [SOLVED] file extensions
    By dottiea in forum Excel General
    Replies: 1
    Last Post: 07-27-2006, 11:20 AM
  6. File Extensions
    By Robin Clay in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 02-25-2006, 11:55 PM
  7. XML file extensions
    By Rowan in forum Excel General
    Replies: 2
    Last Post: 04-22-2005, 03:06 AM

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