Opening/Closing file

Curio

New Member
Joined
Jun 26, 2008
Messages
2
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,
 

Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

Anthony47

Well-known Member
Joined
Mar 29, 2006
Messages
3,422
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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
Code:
With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Documents and Settings\User1\Documents\TREE_A602.txt", Destination _
        :=Range("A1"))

Now, before that lines insert these:
Code:
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
Code:
With ActiveSheet.QueryTables.Add(Connection:= _
        SORG, Destination _
        :=Range("A1"))
[/CODE]

2) add this just before Exit Sub:
Code:
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.
 
Upvote 0

Smitty

Legend
Joined
May 15, 2003
Messages
29,536
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,
 
Upvote 0

Curio

New Member
Joined
Jun 26, 2008
Messages
2
Thank you both very much, that is exactly what I was looking for. You have saved me a lot of time.
 
Upvote 0

Forum statistics

Threads
1,190,783
Messages
5,982,900
Members
439,806
Latest member
ShakeShark1

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top