Attribute VB_Name = "Module1"
Sub Main()
Attribute Main.VB_ProcData.VB_Invoke_Func = "W\n14"

' Make new file
    Workbooks.Add

' Delete sheets 2 and 3
   Application.DisplayAlerts = False
   Sheets("Sheet2").Delete
   Sheets("Sheet3").Delete
   Application.DisplayAlerts = True

' Add titles to Sheet 1
   Range("A1").Select
   ActiveCell.FormulaR1C1 = "Sample no."
   Range("B1").Select
   ActiveCell.FormulaR1C1 = "Reading no."
   Range("C1").Select
   ActiveCell.FormulaR1C1 = "Data"

' Define start point for worksheets and rows

Dim FirstS, LastS, FirstR, LastR, FolderPath, DataRow As Variant
Dim IFirstS, ILastS, IFirstR, ILastR, IFolderPath, IDataRow As String
IFirstS = InputBox("First sample number:")
FirstS = Val(IFirstS)
ILastS = InputBox("Last sample number:")
LastS = Val(ILastS)
IFirstR = InputBox("First reading number:")
FirstR = Val(IFirstR)
ILastR = InputBox("Last reading number:")
LastR = Val(ILastR)
FolderPath = InputBox("Path to folder:", "Folder", "C:\Documents and Settings\")
IDataRow = InputBox("Row to take data from:", "Row")
DataRow = Val(IDataRow)

Dim SheetNo, Row, Sample, Reading As Variant
Let SheetNo = 4
Let Row = 2
Let Sample = FirstS
Let Reading = FirstR

Dim Name, SheetName, FileName As Variant


' Beginning of loop

Do While Sample < LastS + 1

Do While Reading < LastR + 1

' Formats name
If Reading < 10 Then
   Let Name = Sample & "-000" & Reading
ElseIf Reading < 100 Then
       Let Name = Sample & "-00" & Reading
ElseIf Reading < 1000 Then
       Let Name = Sample & "-0" & Reading
End If

Let SheetName = "Sheet" & SheetNo
Let FileName = "TEXT;" & FolderPath & Name & ".txt"

' Import text file into new worksheet

   ActiveWorkbook.Worksheets.Add
   With ActiveSheet.QueryTables.Add(Connection:=FileName, Destination:=Range("A1"))
       .Name = Name
       .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 = True
       .TextFileTabDelimiter = True
       .TextFileSemicolonDelimiter = False
       .TextFileCommaDelimiter = True
       .TextFileSpaceDelimiter = True
       .TextFileColumnDataTypes = Array(1, 1)
       .TextFileTrailingMinusNumbers = True
       .Refresh BackgroundQuery:=False
   End With

' Copy fluorescence value

   Sheets(SheetName).Select
   Range("B" & DataRow).Select
   Selection.Copy

' Add data to main sheet

   Sheets("Sheet1").Select
   Range("C" & Row).Select
   ActiveSheet.Paste
   Range("A" & Row).Select
   ActiveCell.FormulaR1C1 = Sample
   Range("B" & Row).Select
   ActiveCell.FormulaR1C1 = Reading

' Delete data sheet
   Application.DisplayAlerts = False
   Sheets(SheetName).Delete
   Application.DisplayAlerts = True

Let Row = Row + 1
Let SheetNo = SheetNo + 1
Let Reading = Reading + 1

Loop

Let Sample = Sample + 1
Let Reading = 1

Loop

End Sub