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

[Foolzrailer]

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.

 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.

 

[GWteB]

How about ...

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

 

[Foolzrailer]

How about ...

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")

 

[GWteB]

   sht.Cells.Delete

shoul be reverted to

   sht.Cells.Clear

 

[Foolzrailer]

I fixed the last bit by adding the following:

        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"")"

 

[Foolzrailer]

   sht.Cells.Delete

shoul be reverted to

   sht.Cells.Clear

Aah yeah, that fixes it as well. Thank you very much!

 

[GWteB]

You are welcome and thanks for letting me know