Save Excel file according to name of imported text file 1

[KrogerKommandeur]

Hello all!

I have looked for an answer to this question and, to my knowledge, no one has explored this specific issue. When you import a text file into Excel, does it store the file information anywhere? If so, is there a way (through VBA code) to tell Excel to save the file using the original filename, minus the extension?

Thank you in advance for any help. You do not necessarily have to tell me the code; I just need to know where to look or if it's even possible.
Wally -
The Gahndi that eats


Wally -
The Gahndi that eats

 

[dan1967]

If you are using the Data->Get External Data->Import Text File method, you are creating a query table that has the same name as your imported text file, minus the extension. You can look in Excel VBA help for topics pertaining to QueryTables, and more particularly, the Name property, to capture this value and parse to your Excel file name.

Probably something like:

ActiveWorkbook.SaveAs Replace(ActiveSheet.QueryTables(1).Name & ".xls", " ", "_&quot

The "Replace" function, in this instance, removes any spaces in the filename and, well, replaces them with underscores.

Hope this works for you.

Dan.

 

[KrogerKommandeur]

Dan,

Thank you very much! It is always very helpful to know how applications like Excel, Word, Access, etc. perform basic operations like importing. Makes automating things a lot easier. I really wasn't aware of how Excel stored data on imported files, although I suspected that it had to somehow.

I was writing a macro that formats data coming from a text file. Since I'm assuming the person working after me is not very smart, I made everything so that all that they had to do was import the file and run the macro. The final touch was having it save the file.

I do have one more question to ask you, if you just so happen to know. For some reason, Excel does not let me change the keyboard shortcuts for macros that are already existing. Is there a way to hardcode the necessary shortcuts? I mean, buttons and menu items are all good and well, but I do not know how to allow them to be used for more than one file (since the file that is imported and formatted is different every time).

Thanks again!

Wally -
The Gahndi that eats

 

[dan1967]

To set keyboard stokes to a macro, go into Tools->Macro->Macros (or Alt+F8), select the macro you want to assign a keystoke shortcut and click Options. Make sure you don't use any of the pre-existing ones (i.e. Ctrl+c = copy, Ctrl+v = paste, etc.). They will all start with Ctrl, and you can use the Shift and Alt keys to make them more unique.

Dan.

 

[KrogerKommandeur]

I had actually tried that, which was why I had asked. It hadn't worked, and I thought that I had done something by mistake to make it not work.

Wally -
The Gahndi that eats