Replies

Worked a charm,
Thanks

Try this mob, say’s it does almost everything.

http://www.foxitsoftware.com/Secure_PDF_Reader/feature.php

Third from the bottom in the feature list…don’t know if this may do the trick.
They quote;

Text Viewer & Text Converter
View the whole text content of a PDF file.
Convert a whole PDF document into a simple text file.

I think it be the paid version.

Thanks RG,
Interesting coding, I’ll check it out and see how it goes.

Thanks RG,
I’ll test it after a few runs,
that is why I supply a convenient workbook.
To show how I have to have it in other processes.
Not always column B, it’s series of processes and calculations.
Column B was a previous process where it still works OK.

It’s when I try to tweak it to do other columns for other calculations,
it faults.

zeddy, saw the obvious error, missed it.

Code:

If ActiveCell.Value = ActiveCell.Value = “0.000” Then ‘:o

If ActiveCell.Value = “0” Then

RG
tried codes as suggested to, and the original… it goes “AWOL”.

Note though, the top 3 rows ( 1,2 and 3), if blank must remain as they are, not to be deleted.
The Workbook has all examples shown so far.

Thanks

..But just for info, it is always better to delete rows from ‘the bottom up’.

zeddy

Thanks zeddy,
Yes I know there is a correct method/s, a work in progress, I got one of those in a older project not
written by me, but each time I try to work out how to change it’s references, for example this section
here;

Code:

LastRow = Worksheets(“Sheet1”).Range(“B65536”).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Worksheets(“Sheet1”).Cells(i, 7).Value = “” Then
            Worksheets(“Sheet1”).Cells(i, 7).EntireRow.Delete

it goes “AWOL”….

The complete code I have stored, ( and one of my favorite snippets of code ) is;

Code:

Sub DeleteRows()

    Sheets(“Sheet1”).Select
    Sheets(“Sheet1”).Range(“A1”).Select
    Dim LastRow As Integer
    Dim i As Integer
    LastRow = Worksheets(“Sheet1”).Range(“B65536”).End(xlUp).Row
    For i = LastRow To 2 Step -1
        If Worksheets(“Sheet1”).Cells(i, 7).Value = “” Then
            Worksheets(“Sheet1”).Cells(i, 7).EntireRow.Delete
        End If
    Next i
    LastRow = Worksheets(“Sheet1”).Range(“B65536”).End(xlUp).Row

    For i = 1 To LastRow
        Worksheets(“Sheet1”).Cells(i, 1).Value = i – 1
         
 Next i

End Sub

I did try to use it, it may need explaining and some tweaking. It does show as suggested,
deletes Rows correctly.

XP

Thanks all who helped with this phase of my project.

I have uploaded a copy of the workbook, it may need some
code-cleaning-uppers and tweaks for “correctness”, more stuff to learn from.

The workbook,
It may need some cleaning up or more efficient coding…
But it gets the list in the correct order.

Thanks Maud,Zeddy and everyone who helped.

Thanks,
worked perfectly.

Thanks zeddy.

Maybe I am not reading your code right, but I put in;

Code:

zConnection = “URL;http://webpage2.com/gardening/” & zDateString
With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range(“A3”))

It tried to query, but errored 1004

Some history.
Originaly, the old web ( webpage1.com) page, the code was changed to;

Code:

.(Connection = “URL;http://webpage1.com.au/” & Range(“A1”) & “.html”

This worked oerfectly.
It referenced the date from Cell A1

Since webpage1.com.au is no longer,
when recording a new web query macro from

webpage2.com/gardening/2013/3/29/workday

is what is shown in VBA after recording.
The data does get imported, that web address does exist.

However,
To get another new date, one has to go into VBA editor and change that date.
But because the macro is on AutoStart at a specific time of day, it needs to referenced from the date
in Cell A1. Not the DateString from the machine.

What I tried with your zDateString is;

Code:

zConnection = “URL;http://webpage2.com/gardening/” & zDateString
With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range(“A3”))

What I don’t get is, how zdateString syntax “knows” it’s required date is in Sheet1 A1
or am I missing something here ?

I also tried,

Code:

zConnection = “URL;http://webpage2.com/gardening/” & zRange(“A1”) _
With ActiveSheet.QueryTables.Add(Connection:=zConnection, Destination:=Range(“A3”))

Thanks.

No Prob,

I have added another piece of code, this helps if anyone is required to delete unspecified
Rows with Loop, If, Then scenarios

Code:

Sub DeleteRow()

Sheets(“Sorted”).Select
Sheets(“Sorted”).Range(“E3”).Select
Do Until ActiveCell.Value = “”

            ‘current cell          Or cell to Right ( will NOT keep )                   canceled appointments
If ActiveCell.Value = “” Or ActiveCell.Offset(0, 1).Value = “” Or ActiveCell.Offset(0, 1).Value = “Cncld ” Then

‘current cell          Or cell to Right ( will KEEP )                                  canceled appointments
‘If ActiveCell.Value = “” Or ActiveCell.Offset(0, -1).Value = “” Or ActiveCell.Offset(0, 1).Value = “Cncld ” Then

ActiveCell.EntireRow.Delete
ActiveCell.Offset(-1, 0).Select
End If
ActiveCell.Offset(1, 0).Select
Loop

End Sub

PS
After posting, the long bits did not fit entirely on this forum, so it may look wrong due to
word wrapping here, but it works in the VBA Module.

Maudibe,

Thanks,

Have added a bit extra code for “cosmetics”.
I can live with having to do the the “midnite to next day’s date” Ascending Sort by cut and paste to bottom of list manually.
Other than that it’s a good as done for this part of a larger process.

Ok, it’s fixed itself.
The error was that space which came from the RESET sheet. The actual raw data from the source does
not have that, so I guess it’s solved as far as split and formula with macro is concerned.

Thanks

I’ll start a new thread for the rest of the process, I need to work on from some books first.

Thanks all

For the record, I use office 2003 on XP, but have office 2010 on another PC getting fixed.

rory, the formula is fine.

Only Cell C4 has the issue… Even when I tried deleting all columns C to H,
Then I deleted Sheet2 and, Inserted a brand new Sheet.
Cell C4 has a problem no matter what Format I tried.

After the macro is Run, if I type in the same number in Cell C4, it’s corrects itself.
The #VALUE! becomes the calculation, in this case, 3.85

It goes from 26 to 26.00 when 26 is typed, only that cell. !!

This is not what I initially posting about, it’s a new problem.

RetiredGeek and rory,
I have put both macro in the same workbook for testing, plus I added some “Reset”
conveniences for testing the project.

I get errors in the formula after the macro is Run, if there are 0’s ( zero’s).

I haven’t tested the other replies yet, but will do so.

And later on I do have a piece of macro somewhere, but in Column B3 to B32, if there is a 0, I will need
to delete that entire row.

Thanks all for your help so far.

PS
There will be another thread started once this part of the process is sorted, more formula to convert
Column H to decimal and fractions in a certain way.
Or do I keep the same project on this thread?