0

I am trying to make a macro where I prompt the user to select a text file, and then have the contents of the selected text file imported to a cell. I managed to get the filedialog and have the user select the file, but then I don't know how to proceed with the importing of the file. Could someone help with this?

This is the (working) code I have so far:

 Sub ImportFile()
Dim dFile As FileDialog, result As Integer, it As Variant
Set dFile = Application.FileDialog(msoFileDialogOpen)

dFile.InitialFileName = "G:\"


If dFile.Show = -1 Then
  Debug.Print dFile.SelectedItems(1)
End If


End Sub

Thank you in advance!

Rhyfelwr
  • 299
  • 2
  • 5
  • 19
  • You want to place the whole contents of a file in a single _cell_?? Is the file multi-line? – Paul Ogilvie Nov 24 '17 at 13:48
  • Anyway, look for the `Open` statement in Help. Then get the contents into a variable (lookup `Line Input`), then put the variable in the cell with `ActiveSheet.Cells(1,1).Value = myVar` Replace 1,1 with the cell's coordinates. – Paul Ogilvie Nov 24 '17 at 13:51
  • Yeah it's a text file with a bunch of lines, trying to import it as delimited text separated into columns by space. What do you mean by Open statement? – Rhyfelwr Nov 24 '17 at 13:52
  • Please provide an example of the data, e.g. two lines. – Paul Ogilvie Nov 24 '17 at 13:53
  • Таблица: "FM_USER" Операция: "I" Пользователь: "AF12345" Дата: "30/06/2017 09:56:11" Поле: "USER_ID" Старое значение: "" Новое значение: "AC23572" Поле: "USER_INACTIVITY_DROP" Старое значение: "" Новое значение: "Y" Поле: "USER_LEVEL" Старое значение: "" Новое значение: "1" – Rhyfelwr Nov 24 '17 at 13:56
  • Basically the text file is a bunch of this repeating a few hundred times over time with a header. I am trying to write a macro to import, format and decipher a Russian security report. – Rhyfelwr Nov 24 '17 at 13:57

1 Answers1

0

You are after something like the code below, it will import your entire .txt. file, to a 2-d array. Afterwards, you need to loop through the rows dimension, and copy each row's columns into your worksheet, starting from row 1.

Code

Option Explicit

Sub ImportFile()

Dim fNum As Integer
Dim WholeFile As String
Dim FileName As String
Dim Arr As Variant, ArrRow As Variant
Dim i As Long, Col As Long

Dim dFile As FileDialog, result As Integer, it As Variant

Set dFile = Application.FileDialog(msoFileDialogOpen)
dFile.InitialFileName = "G:\"

If dFile.Show = -1 Then
    FileName = dFile.SelectedItems(1)
End If

fNum = FreeFile
Open FileName For Input As fNum
WholeFile = Input$(LOF(fNum), #fNum)
Close fNum

Arr = Split(WholeFile, vbCrLf) ' Break the file into lines (array) change vbCrLf to your rows delimeter

' loop through Array rows
For i = 0 To UBound(Arr) - 1
    ArrRow = Split(Arr(i), ",")   '  break the row to columns , change "," to your delimeter
    Range("A" & i + 1).Resize(1, UBound(ArrRow)).Value = ArrRow
Next i

End Sub
Shai Rado
  • 33,032
  • 6
  • 29
  • 51
  • If Space is the delimiter then do I put " " or ""? Also do I need to give the exact number of rows at vbCrLf? Because the number of potential rows is random. – Rhyfelwr Nov 24 '17 at 14:42
  • @Rhyfelwr if it's Space the `" "`. And no, you don't need to give the exact number of rows, that's what nice about it, it's dynamic. Let me know if it works as you intended – Shai Rado Nov 24 '17 at 15:28
  • Hello, quick question how do I define tab as a delimiter in this code? If I put `" |"` I get application or object defined error. Also at this line: `Range("A" & i + 1).Resize(1, UBound(ArrRow)).Value = ArrRow` should I put "A1" or something instead of A, as I get error 1004 – Rhyfelwr Nov 28 '17 at 09:40
  • Nvm I found that it is vbTab, I got the error because I didn't properly define the delimiter. Thanks for the help. – Rhyfelwr Nov 28 '17 at 09:47