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.
Bookmarks