how to autofill data

B

barb666

New member
Joined
Jul 16, 2014
Messages
7
Reaction score
0
Points
0
Hi, please bear with me; first time poster. Not sure this is even the right forum for my query!

I have data in notepad format that I want to import into a spreadsheet. It comprises time in one minute intervals and the number of events in that period (see below). It can run into 400+ rows of data.

TIME NO
22:00:00 1
22:04:00 4
22:07:00 2

I want to know whether I can paste the data into excel in a way where excel automatically fills in the missing 1-minute intervals and adds a 0 (shown in bold) so that the data looks like the list below

TIME NO
22:00:00 1
22:01:00 0
22:02:00 0
22:03:00 0
22:04:00 4
22:05:00 0
22:06:00 0
22:07:00 2

Any help would be much appreciated
Regards
Dave
 
If the data file is space delimited then run a tweaked version of:
Code: 
Sub blah()
Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
FileNumber = FreeFile
DestRow = 2
Open "data.txt" For Input As #FileNumber    ' Open file.
Do  'loop in case there are text headers
  Line Input #FileNumber, TextLine    ' Read line into variable.
  x = Split(Application.Trim(TextLine))
  If Not IsDate(x(0)) Then
    Cells(DestRow, 1).Resize(, 2).Value = x
    DestRow = DestRow + 1
  End If
Loop Until IsDate(x(0))
mytime = TimeValue(x(0))
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = x(1)
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:00")
Do While Not EOF(FileNumber)    ' Loop until end of file.
  Line Input #1, TextLine
  x = Split(Application.Trim(TextLine))
  NextTime = TimeValue(x(0))
  Do Until NextTime <= mytime
    Cells(DestRow, 1).Value = mytime
    Cells(DestRow, 2).Value = 0
    Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:0")
  Loop
  Cells(DestRow, 1).Value = mytime
  Cells(DestRow, 2).Value = x(1)
  mytime = mytime + TimeValue("00:01:00")
  DestRow = DestRow + 1
Loop
Close #FileNumber    ' Close file.
End Sub
 
Last edited:
Many thanks for taking the time to reply but I'm a zoologist not a computer programmer, and without further guidance, I have absolutely no idea how to apply your answer.

I was hoping for instructions as to which buttons to press on the toolbar!
 
barb666 said:
but I'm a zoologist not a computer programmer, and without further guidance, I have absolutely no idea how to apply your answer.
I was hoping for instructions as to which buttons to press on the toolbar!
Click to expand...
Writing out instructions would take me far too long, especially as I don't know what delimites the data in your text file.

Solution 1. I can point you to http://www.excelguru.ca/content.php?265-Adding-VBA-Code-For-The-First-Time-User but you will still need to tweak it with the right file name and put the text file in the right place.
If you go down this route, come back and tell me how you fare.

Solution 2. We could do a remote assistance session with the likes of TeamViewer, perhaps along with a 'phone call, to show you how to do it by pressing buttons on the toolbar.
 
Ok, I think I've created a macro (I've saved it as an excel macro-enabled worksheet with a .xlsm file extension). I get a Run Time error '53' when I run it. I assume this is due to the file name/file location issue you mention? How do I resolve this?

Many thanks in advance
 
The line:
Open "data.txt" For Input As #FileNumber ' Open file.
needs to become something along the lines of:
Open "C:\Users\barb666\Documents\excelguru3265\data.txt" For Input As #FileNumber ' Open file.
that is, the full path to the file. You can get this by opening an instance of Windows Explorer and navigating to the file, then Shift and right-click on the file icon and choose Copy as path, which will put the whole path and the file name into the clipboard, including quote marks. You need to paste this into the code replacing:
"data.txt"

Don't forget the code still assumes a space-delimited file; if it's not, tell me and I will adjust. (Where does the file come from?)
 
Last edited:
I've inserted the correct filepath into the code. As far as I know the data are space delimited - it comes in a text file opened with notepad. When I copy and paste it into excel it automatically fits neatly into columns, but it might not be, so just in case can you explain please?

I run the macro and get 'Run time error 64 Input past end of file' message. When I press debug the following line of code is highlighted in yellow:

Line Input #FileNumber, TextLine ' Read line into variable.

The macro has imported the data into a spreadsheet but instead of reading 21:00 1 it reads 21:001

Also, it has not inserted the rows that don't have data
 
Then it's likely to be tab delimited. There are two lines like:
x = Split(Application.Trim(TextLine))

change them both to:
x = Split(Application.Trim(TextLine), vbTab)
 
Wow, that did the trick! Now in Column A I have consecutive minutes from my first data point to my last, and in column B I have 1's from my data and 0's to fill the gaps - just what I wanted.

1. I've noticed that some of the 1's from my data have been inserted into the following minute rows, so they are a 1 minute late. Why would this be?
2. My data sometimes contain other numbers besides 1, usually no more than 6. What line of code would Insert to make the macro interpret any number as 1?
3. The coded spreadsheet starts and ends with my first and last data points, eg 21:30 and 04:45. I would like the spreadsheet to start at say 21:00 and end at 05:00, adding 0's either side of my data . What code would insert to achieve this?

Really making progress - many thanks
 
barb666 said:
1. I've noticed that some of the 1's from my data have been inserted into the following minute rows, so they are a 1 minute late. Why would this be?
Click to expand...
No idea, attach (Go Advanced when replying and click Manage attachments) or send me a sample .txt file where this happens.



barb666 said:
2. My data sometimes contain other numbers besides 1, usually no more than 6. What line of code would Insert to make the macro interpret any number as 1?
Click to expand...
Change both instances of:
Cells(DestRow, 2).Value = x(1)
to:
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
which will convert any number greater or equal to 1 into a 1, anything else, a zero.



barb666 said:
3. The coded spreadsheet starts and ends with my first and last data points, eg 21:30 and 04:45. I would like the spreadsheet to start at say 21:00 and end at 05:00, adding 0's either side of my data . What code would insert to achieve this?
Click to expand...
Try this (after adjustment for file name):
Code: 
Sub blah()
Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
FileNumber = FreeFile
DestRow = 2
Open "data.txt" For Input As #FileNumber    ' Open file.
Do  'loop in case there are text headers
  Line Input #FileNumber, TextLine    ' Read line into variable.
  x = Split(Application.Trim(TextLine), vbTab)
  If Not IsDate(x(0)) Then
    Cells(DestRow, 1).Resize(, 2).Value = x
    DestRow = DestRow + 1
  End If
Loop Until IsDate(x(0))
mytime = TimeValue(x(0))
Cells(DestRow, 1).Value = mytime
'Cells(DestRow, 2).Value = x(1)
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:00")
  mytime = mytime - Int(mytime)

Do While Not EOF(FileNumber)    ' Loop until end of file.
  Line Input #1, TextLine
  x = Split(Application.Trim(TextLine), vbTab)
  NextTime = TimeValue(x(0))
  Do Until Round(NextTime, 5) = Round(mytime, 5)
    Cells(DestRow, 1).Value = mytime
    Cells(DestRow, 2).Value = 0
    Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:0")
  mytime = mytime - Int(mytime)
  Loop
  Cells(DestRow, 1).Value = mytime
  'Cells(DestRow, 2).Value = x(1)
  Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
  mytime = mytime + TimeValue("00:01:00")
  mytime = mytime - Int(mytime)
  DestRow = DestRow + 1
Loop
Close #FileNumber    ' Close file.
End Sub
Sub blah2()
Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
FileNumber = FreeFile
DestRow = 2
'Open "data.txt" For Input As #FileNumber    ' Open file.
Open "C:\Users\Pascal\Documents\excelguru3265\datatab.txt" For Input As #FileNumber    ' Open file.

Do  'loop in case there are text headers
  Line Input #FileNumber, TextLine    ' Read line into variable.
  x = Split(Application.Trim(TextLine), vbTab)
  If Not IsDate(x(0)) Then
    Cells(DestRow, 1).Resize(, 2).Value = x
    DestRow = DestRow + 1
  End If
Loop Until IsDate(x(0))
'fill in from 9pm until first time in file:
mytime = TimeValue("21:00:00")
Do Until Round(TimeValue(x(0)), 5) = Round(mytime, 5)
  Cells(DestRow, 1).Value = mytime
  Cells(DestRow, 2).Value = 0
  Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
  DestRow = DestRow + 1
  mytime = mytime + TimeValue("00:01:0")
  mytime = mytime - Int(mytime)
Loop
'print the first file time into the sheet:
Cells(DestRow, 1).Value = mytime
Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
DestRow = DestRow + 1
mytime = mytime + TimeValue("00:01:0")
mytime = mytime - Int(mytime)
'process the rest of the file times:
Do While Not EOF(FileNumber)    ' Loop until end of file…
  Line Input #1, TextLine
  x = Split(Application.Trim(TextLine), vbTab)
  NextTime = TimeValue(x(0))
  Do Until Round(NextTime, 5) = Round(mytime, 5) '…filling in missing times:
    Cells(DestRow, 1).Value = mytime
    Cells(DestRow, 2).Value = 0
    Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:0")
    mytime = mytime - Int(mytime)
  Loop
  
  Cells(DestRow, 1).Value = mytime
  Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
  mytime = mytime + TimeValue("00:01:00")
  mytime = mytime - Int(mytime)
  DestRow = DestRow + 1
Loop
Close #FileNumber    ' Close file.
'fill in after last time to 5am
Do Until Round(TimeValue("05:01:00"), 5) = Round(mytime, 5)
  Cells(DestRow, 1).Value = mytime
  Cells(DestRow, 2).Value = 0
  Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
  DestRow = DestRow + 1
  mytime = mytime + TimeValue("00:01:0")
  mytime = mytime - Int(mytime)
Loop

End Sub
I've been lazy and used several loops when with bit more logic I could probably have done it in one loop, but it seems to work.
 
That works beautifully, thank you very much!

One last thing for now before I try it out on lots of data, if I want my data to stand out a bit more in the spreadsheet is there a line of code that would turn it red? I confess that I did try and swap 'Bold' for 'Red' in the code in the naive hope that it would be that easy (see below), but it didn't work.

Cells(DestRow, 1).Resize(, 2).Font.Red = True

Thanks again
 
change all instances (3 of them) to:
Cells(DestRow, 1).Resize(, 2).Font.Color = vbRed
 
Very impressive. I never realised that excel could this!

I've been inputting this data manually for years, you have no idea how much time you have just saved me.

Its time to learn VBA code I think...

Many thanks
Dave
 
You must log in or register to reply here.
Back
Top