Macro to insert most recent .prn file data into spreadsheet

**martynmordecai** · 04-03-2013, 11:36 AM

Hey

I have been using a spreadsheet for about a year now where i have to manually input allot of data into throughout the week.
Im looking at having a system where i can insert data from the most recently saved .prn in my docs into the spreadsheet.

The spreadsheet consists of 7 main tabs (one for each day of the week).

I would like a button on each page which can insert the data to that specific tab when pressed

I have attached the Spreadsheet and because it wont let me upload a .PRN file i have converted it to a .TXT file for your viewing.

Thanks

Martyn

**martynmordecai** · 04-04-2013, 08:40 PM

Here is a macro i have come up with.
But i want something that inputs the most recently modified document in "My Docs" into my worksheet as the name of the .prn file will change everyday

Sub adddata()
'
' adddata Macro
'

'
Range("AC6:AK32").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\Martyn Mordecai\Desktop\speedofservice.prn", Destination:= _
Range("$AC$6"))
.Name = "speedofservice"
.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 = xlFixedWidth
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileColumnDataTypes = Array(2, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileFixedColumnWidths = Array(6, 10, 10, 10, 7, 9, 6, 2, 9, 6, 8, 9, 14, 5, 15, 8)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Range("AB6").Select
End Sub