VBA - Import text to specific sheet, with delimited with file dialog prompt

Foolzrailer

New Member
Joined
Jun 12, 2017
Messages
15
Hello

I'm trying to import a text file into a specific sheet, where it should do a Text to columns with the delimiter set as a space.

What I would like to do:
Clear all data in Sheet "SVKData"
File Prompt for a text file, that imports data into "SVKData" the data should just be imported into A1 (the text is all in one column when added)
Text to Column with the Delimiter set to a space (Chr(32)?). I think this can be done while importing unsure though.
Remove Rows 1-7 on "SVKData"

I've gotten a bit of the way from googling I'm a novice in vba, but I'm struggling with the import file dialog function.


VBA Code:
 Sub ImportTextFile()
 Sheets("SVKData").Cells.Clear

    Dim fileToOpen As Variant
    Dim fileFilterPattern As String
    Dim wsMaster As Worksheet
    Dim wbTextImport As Workbook
       
    fileFilterPattern = "Text Files (*.txt; *.csv), *.txt; *.csv"
 
    
    fileToOpen = Application.GetOpenFilename(fileFilterPattern)
    
    
    If fileToOpen = False Then
            ' Input cancelled
            MsgBox "No file selected."
        Else
 'Missing what to do here. 
    
    End If
    
ActiveWorkbook.Worksheets("SVKData").Range("1:7").EntireRow.Delete  

End Sub

Any help on this would be much appreciated.
 

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,407
Office Version
  1. 2013
Platform
  1. Windows
How about ...

VBA Code:
Public Sub Foolzrailer()

    Dim fileToOpen As Variant
    Dim fileFilterPattern As String
    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets("SVKData")
    sht.Cells.Delete

    fileFilterPattern = "Text Files (*.txt; *.csv), *.txt; *.csv"

    fileToOpen = Application.GetOpenFilename(fileFilterPattern)

    If fileToOpen = False Then
        ' Input cancelled
        MsgBox "No file selected."
    Else
        ImportCSV fileToOpen, 8, sht.Range("A1")
    End If

    'ActiveWorkbook.Worksheets("SVKData").Range("1:7").EntireRow.Delete
End Sub

Public Sub ImportCSV(ByVal argCSV_FullName As String, ByVal argStartRow As Long, ByVal argDest As Range)
    With argDest.Parent.QueryTables.Add(Connection:="TEXT;" & argCSV_FullName, Destination:=argDest)
        .TextFileStartRow = argStartRow
        .TextFileParseType = xlDelimited
        .TextFileSpaceDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub
 
Solution

Foolzrailer

New Member
Joined
Jun 12, 2017
Messages
15
How about ...

VBA Code:
Public Sub Foolzrailer()

    Dim fileToOpen As Variant
    Dim fileFilterPattern As String
    Dim sht As Worksheet

    Set sht = ThisWorkbook.Sheets("SVKData")
    sht.Cells.Delete

    fileFilterPattern = "Text Files (*.txt; *.csv), *.txt; *.csv"

    fileToOpen = Application.GetOpenFilename(fileFilterPattern)

    If fileToOpen = False Then
        ' Input cancelled
        MsgBox "No file selected."
    Else
        ImportCSV fileToOpen, 8, sht.Range("A1")
    End If

    'ActiveWorkbook.Worksheets("SVKData").Range("1:7").EntireRow.Delete
End Sub

Public Sub ImportCSV(ByVal argCSV_FullName As String, ByVal argStartRow As Long, ByVal argDest As Range)
    With argDest.Parent.QueryTables.Add(Connection:="TEXT;" & argCSV_FullName, Destination:=argDest)
        .TextFileStartRow = argStartRow
        .TextFileParseType = xlDelimited
        .TextFileSpaceDelimiter = True
        .Refresh BackgroundQuery:=False
    End With
End Sub

This works brilliantly, I was just experimenting with some of Allen Wyatts code, but this does the trick perfectly.

Bonus question though: On a different Sheet "Calculation" I have some calculations based on information in E:E and F:F on "SVKData". However when I use this VBA code these calculations just return #REFERENCE! error, and I have to manually type in the specific columns again. Is it possible to avoid this reference error?

Formulas in cells:
=CONVERT(SUM(SVKData!E:E);"day";"yr")+KONVERTER(SUM(SVKData!F:F);"day";"yr")
 
Last edited:

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,407
Office Version
  1. 2013
Platform
  1. Windows
Rich (BB code):
   sht.Cells.Delete

shoul be reverted to
Rich (BB code):
   sht.Cells.Clear
 

Foolzrailer

New Member
Joined
Jun 12, 2017
Messages
15

ADVERTISEMENT

I fixed the last bit by adding the following:

VBA Code:
        Worksheets("BeregnNedbrud").Range("B4").FormulaLocal = "=Konverter(SUM(SVKDATA!E:E);""day"";""yr"")+Konverter(SUM(SVKDATA!F:F);""day"";""yr"")"
        Worksheets("BeregnNedbrud").Range("C4").FormulaLocal = "=Konverter(SUM(SVKDATA!E:E);""day"";""day"")+Konverter(SUM(SVKDATA!F:F);""day"";""day"")"
        Worksheets("BeregnNedbrud").Range("D4").FormulaLocal = "=Konverter(SUM(SVKDATA!E:E);""day"";""hr"")+Konverter(SUM(SVKDATA!F:F);""day"";""hr"")"
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,407
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting me know :)
 

Forum statistics

Threads
1,147,482
Messages
5,741,404
Members
423,657
Latest member
Medrok2021

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