Importing text file into excel sheet

Question

I m writing a VBA code which supposed to delete the data on a selected excel sheet  open a dialog box for text file selection  and then import the data from that text file to the same exact sheet I ve deleted the data from  So far I can only open the text file into a new workbook but can t open it to the same sheet I ve deleted the data from   Here s what I came with so far  will appreciate your help   Dim Filt As String Dim FilterIndex As Integer Dim Title As String Dim FileName As Variant  Filt    Cst Files    prn    prn  Title    Select a cst File to Import  FileName   Application GetOpenFilename FileFilter  Filt  Title  Title   If FileName   False Then MsgBox  No File Was Selected  Exit Sub End If  With Application ActiveSheet     Cells Select Selection QueryTable Delete Selection ClearContents End With  Workbooks Open FileName

User · Answer

I think my answer to my own question here is the simplest solution to what you are trying to do:

Select the cell where the first line of text from the file should be.
Use the Data/Get External Data/From File dialog to select the text file to import.
Format the imported text as required.
In the Import Data dialog that opens, click on Properties...
Uncheck the Prompt for file name on refresh box.
Whenever the external file changes, click the Data/Get External Data/Refresh All button.

Note: in your case, you should probably want to skip step #5.

User · Answer

you can write            WorkbookConnection Delete after            Refresh BackgroundQuery  False this will delete text file external connection

User · Answer

There are many ways you can import Text file to the current sheet  Here are three  including the method that you are using above    Using a QueryTable Open the text file in memory and then write to the current sheet and finally applying Text To Columns if required  If you want to use the method that you are currently using then after you open the text file in a new workbook  simply copy it over to the current sheet using Cells Copy   Using a QueryTable  Here is a simple macro that I recorded  Please amend it to suit your needs   Sub Sample       With ActiveSheet QueryTables Add Connection              TEXT C  Sample txt   Destination  Range   A 1                        Name    Sample           FieldNames   True          RowNumbers   False          FillAdjacentFormulas   False          PreserveFormatting   True          RefreshOnFileOpen   False          RefreshStyle   xlInsertDeleteCells          SavePassword   False          SaveData   True          AdjustColumnWidth   True          RefreshPeriod   0          TextFilePromptOnRefresh   False          TextFilePlatform   437          TextFileStartRow   1          TextFileParseType   xlDelimited          TextFileTextQualifier   xlTextQualifierDoubleQuote          TextFileConsecutiveDelimiter   False          TextFileTabDelimiter   True          TextFileSemicolonDelimiter   False          TextFileCommaDelimiter   True          TextFileSpaceDelimiter   False          TextFileColumnDataTypes   Array 1  1  1  1  1  1           TextFileTrailingMinusNumbers   True          Refresh BackgroundQuery  False     End With End Sub   Open the text file in memory  Sub Sample       Dim MyData As String  strData   As String      Open  C  Sample txt  For Binary As  1     MyData   Space  LOF 1       Get  1    MyData     Close  1     strData     Split MyData  vbCrLf  End Sub   Once you have the data in the array you can export it to the current sheet   Using the method that you are already using  Sub Sample       Dim wbI As Workbook  wbO As Workbook     Dim wsI As Worksheet      Set wbI   ThisWorkbook     Set wsI   wbI Sheets  Sheet1     lt    Sheet where you want to import      Set wbO   Workbooks Open  C  Sample txt        wbO Sheets 1  Cells Copy wsI Cells      wbO Close SaveChanges  False End Sub   FOLLOWUP  You can use the Application GetOpenFilename to choose the relevant file  For example     Sub Sample       Dim Ret      Ret   Application GetOpenFilename  Prn Files    prn     prn        If Ret  lt  gt  False Then         With ActiveSheet QueryTables Add Connection              TEXT    amp  Ret  Destination  Range   A 1                     gt  Rest of the code          End With     End If End Sub

[excel] Importing text file into excel sheet

Examples related to excel

Examples related to vba