Macro to load text file not working.

**calvinle** · 03-14-2014, 04:33 PM

Hi,

I need to load a text file into an excel, but to paste all only in the column A and not having to split them out to other column.

Here are the sample files:
HELP.xlsm
HELP.txt

If anyone can tell me where is wrong?

Thanks.

**apo** · 03-14-2014, 05:56 PM

Hi..

Try this.. change the file path to suit..

Sub Test()
Dim x
   x = Split(CreateObject("scripting.filesystemobject").opentextfile("C:\HELP.txt").readall, vbCrLf)
   Sheets("Original").Range("A1").Resize(UBound(x)).Value = Application.Transpose(x)
End Sub

or this with a File Browser Dialogue..

Sub Test1()
 Dim x, fName
    fName = Application.GetOpenFilename("Text Files (*.txt),*.txt")
    If fName <> "False" Then
        x = Split(CreateObject("scripting.filesystemobject").opentextfile(fName).readall, vbCrLf)
        Sheets("Original").Range("A1").Resize(UBound(x)).Value = Application.Transpose(x)
    End If
End Sub

**jindon** · 03-14-2014, 06:36 PM

Try change

Set sht = wb.Worksheets("Sheet1")

to

Set sht = wb.Worksheets(1)

**calvinle** · 03-20-2014, 10:53 PM

The final code is:

Sub Open_File()
   Dim wb                          As excel.Workbook
   Dim wsActive                    As excel.Worksheet
   Dim sht                         As excel.Worksheet
   Dim f                           As Object

   Set wsActive = ActiveSheet
   Set f = Application.FileDialog(3)
   f.AllowMultiSelect = False
   f.Show

   Set wb = Workbooks.Open(f.SelectedItems(1))
   Set sht = wb.Worksheets(1)
   wsActive.UsedRange.ClearContents
   LR = sht.UsedRange.SpecialCells(xlLastCell, xlNumbers).Row
   sht.Range("A1:L" & LR).Copy
   wsActive.Range("A1").PasteSpecial Paste:=xlPasteValues
   Application.CutCopyMode = False
   wb.Close False
End Sub

But how can I mod so if they cancel and dont load any file, it will prompt a message saying, you did not select any file?

Thanks.

**jindon** · 03-20-2014, 11:05 PM

Try change

   f.Show

to

If Not f.Show Then
    MsgBox "you did not select any file?"
    Exit Sub
End If

Macro to load text file not working.

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Macro to load text file not working.

Re: Macro to load text file not working.

Re: Macro to load text file not working.

Re: Macro to load text file not working.

Re: Macro to load text file not working.

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro Working fine until last line of text file

VBA Importing Text File makes the file longer to load everytime

How to load macro file Automatically in Excel

Opening a browse and load file box in a macro

Can I load text file into Excel seting field delimter using cmd li

Bookmarks

Bookmarks

Posting Permissions