Opening/Closing file

[Curio]

Hi guys, first time poster. I have what seems to be a very simple problem but I am having issues with it. I am looking for a basic macro, which when run: 1. Gives the option for file open (*.txt format of any name) 2. Copies the contents into the workbook of the macro 3. Closes the inital text file I've tried quite a lot of things and done quite a few searches, but came back with nothing. So apologies if this has been asked before, if it has, please could you link the answer? Thanks,

 

[Anthony47]

A new User deserve an answer, let me try.
You can import a txt file into your spreadsheet via Menu /Data /Import external data (*) and we can use this feature to prepare a macro.

So, start recording a macro (Menu /Macro /Record new macro) while you do the following things:
-select all the cells of the current sheet and clear them (assuming that you want start from a blank worksheet...)
-Select A1
-Menu /Data /Import external data (*) /Import data (*); in the Type of text box, select "Text file"; browse your file system and select any txt file to open
-the Import wizard will open; select Delimited, Next; chech Other (*) and input a "tilde" as the delimiting character (to type a tilde, use the sequence Alt-126 using the numeric pad). With this I assume that your text will never contain a "tilde"; if you cannot be sure of this, then select Fixed lenght (and not Delimited), Next, then remove any separator (a vertical bar) that could have set by default (to remove, doubleclick on them)
-Next, and you'll be at step 3 of 3; I suggest that you format the column as Text (+)
-Finish
-confirm A1 as the destination address, Ok
At this point stop recording the macro (Menu /Macro /Stop recording)

At this point open the vba to edit the macro:
-Menu /Macro /Macro; select the name of your macro (Macro1, maybe) and press Modify (*)

You will read the code of the macro, and the first lines will look like these

With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\User1\Documents\TREE_A602.txt", Destination _
        :=Range("A1"))

Now, before that lines insert these:

Reselex:
FullNamex = Application.GetOpenFilename(Filefilter:="Txt Files (*.txt), *.xls,Tutti (*.*),*.*", Title:="Seleziona file")
Mess = ">>Selected " & FullNamex & vbCrLf & ">>  Yes to Confirm; NO to Change selection; CANCEL to Exit"
Selex = MsgBox(Prompt:=Mess, Buttons:=vbYesNoCancel)
If Selex = 2 Then GoTo Ext    'Cancel
If Selex = 7 Then GoTo Reselex   'NO = Re-select
SORG = "TEXT; " & FullNamex

And finally modify the original code
1) replace this "TEXT;C:\Documents and Settings\User1\Documents\TREE_A602.txt" (including the "quotation marks") with SORG
So it could look like

With ActiveSheet.QueryTables.Add(Connection:= _
        SORG, Destination _
        :=Range("A1"))

[/CODE]

2) add this just before Exit Sub:

Ext:

Thats all.
The new macro will allow you to select a txt file from the filesystem, clear the worksheet and import this txt.

Notes
(*) all the messages marked (*) could be displayed with different words (my excel is not the english version)
(+) If you wish to import the txt and parse its lines in columns, then modify accordingly the selections on the Import wizard; what I suggested will import each line as a single cell.
When recording the macro, please try doing only what suggested, as any action (ie selecting cells, scrolling down, rigt, left,...) could result in additional, useless code (but not harmful).

If you need further information, just post again.

Bye.

 

[Smitty]

Welcome to the Board!

How's this:

<font face=Tahoma><SPAN style="color:#00007F">Sub</SPAN> Open_CopyfromTextFile()<br>    <SPAN style="color:#007F00">'   Launch the File Open dialog to browse for a text file</SPAN><br>    <SPAN style="color:#007F00">'   Copy its contents to the activesheet, then close without saving</SPAN><br>    <br>    <SPAN style="color:#00007F">Dim</SPAN> FileName <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> Filt <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>, Title <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> FilterIndex <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN>, Response <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Long</SPAN><br>    <SPAN style="color:#00007F">Dim</SPAN> CurrentSheet <SPAN style="color:#00007F">As</SPAN> Worksheet<br>        <br>    <SPAN style="color:#007F00">'   Set Current Sheet</SPAN><br>        Set CurrentSheet = ActiveSheet<br>    <SPAN style="color:#007F00">'   Set Drive letter</SPAN><br>        ChDrive "C:\"<br>    <SPAN style="color:#007F00">'   Set to Specified Path\Folder</SPAN><br>        ChDir "C:\Documents and Settings\All Users\Desktop\"<br>    <SPAN style="color:#007F00">'   Set File Filter</SPAN><br>        Filt = "Text Files (*.txt), *.txt"<br>    <SPAN style="color:#007F00">'   Set *.* to Default</SPAN><br>        FilterIndex = 5<br>    <SPAN style="color:#007F00">'   Set Dialogue Box Caption</SPAN><br>        Title = "Please select a different File"<br>    <SPAN style="color:#007F00">'   Get FileName</SPAN><br>        FileName = Application.GetOpenFileName(FileFilter:=Filt, _<br>            FilterIndex:=FilterIndex, Title:=Title)<br>    <SPAN style="color:#007F00">'   Exit if Dialogue box cancelled</SPAN><br>        <SPAN style="color:#00007F">If</SPAN> FileName = <SPAN style="color:#00007F">False</SPAN> <SPAN style="color:#00007F">Then</SPAN><br>            Response = MsgBox("No File was selected", vbOKOnly & vbCritical, "Selection Error")<br>            <SPAN style="color:#00007F">Exit</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN><br>    <SPAN style="color:#007F00">'   Display Full Path & File Name</SPAN><br>        Response = MsgBox("You selected " & FileName, vbInformation, "Proceed")<br>    <SPAN style="color:#007F00">'   Open Selected Workbook</SPAN><br>        Workbooks.Open FileName<br>    <br>    <SPAN style="color:#007F00">'   Copy Text File</SPAN><br>        ActiveSheet.UsedRange.Copy CurrentSheet.Range("A1")<br>    <SPAN style="color:#007F00">'   Close Text File w/o saving</SPAN><br>        ActiveWorkbook.Close <SPAN style="color:#00007F">False</SPAN><br>    <br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Hope that helps,

 

[Curio]

Thank you both very much, that is exactly what I was looking for. You have saved me a lot of time.