Pasting Dates from Clipboard of Web Page to Excel

Hi from Europe!

I am using VBA to paste a clipboard filled with figures and dates from a Web
page but the VBA is altering 7 March 2006 (07/03/06) to 3 July 2006
(03/07/2006). This only happens in VB code - when pasting manually the date
is pasted fine.

I have searched the newsgroups but the only solution seems to be to use
Excel 2002 which I don't have.

The date is always in the third column of the clipboard - is it possible to
interrogate the clipboard? Are there any other avenues to explore?

Thanks in advance

Tim

Tim,

What does your code look like? If you can get the text from the clipboard
then you might be able to extract the date and format it as you want....

Tim


"Tim Childs" <tsnip1@yahoo.co.uk> wrote in message
news:e1I5CqhQGHA.1556@TK2MSFTNGP09.phx.gbl...
> Hi from Europe!
>
> I am using VBA to paste a clipboard filled with figures and dates from a
> Web
> page but the VBA is altering 7 March 2006 (07/03/06) to 3 July 2006
> (03/07/2006). This only happens in VB code - when pasting manually the
> date
> is pasted fine.
>
> I have searched the newsgroups but the only solution seems to be to use
> Excel 2002 which I don't have.
>
> The date is always in the third column of the clipboard - is it possible
> to
> interrogate the clipboard? Are there any other avenues to explore?
>
> Thanks in advance
>
> Tim
>
>

Hi

Thanks for responding.

I have copied the code for the proc. below. You can see what I have tried on
the pasting and sendkeys front but it did not get me anywhere ( paradigm for
life?!!).

The ability to do the interrogation of the clipboard would be helpful

Any suggestions welcome

Tim


Sub ReformatBanklineBalanceTable()
'
'Need to remove the currency option of 4 d.p. from Autoformat
'module (in proc.FormatData_VISIBLE) - TPC, 7 March 2006

' Macro recorded 01/03/2006 by tchilds
'
'NOTE: other users may export more columns of balances than me
'
Dim sTitleString As String ' need to add at end of macro
Dim NewLastCol
Dim NewLastRow

Dim LastRowNonBlank As Long 'Last Row with content
Dim LastColNonBlank As Integer 'Last column with content - cannot exceed 256

Dim iColWithDate
Dim TempWb As Object

Dim sDateFormat As String
Dim sNumberFormat As String
Dim CurrWkBk As Object

Application.ScreenUpdating = False

bSkipAlertForFormula = True 'stops an alert coming up warning about cells
with
'formulae - not required out of P'soft Query download context

sDateFormat = "d-mmm-yy"
sNumberFormat = "#,##0.00_);[Red]-#,##0.00_)"

Set CurrWkBk = ActiveWorkbook

Call Workbook_Add

Set TempWb = ActiveWorkbook
CurrWkBk.ActiveSheet.Cells.Copy

'NOTE WELL: need to fill in clipboard when it was filled within Excel
environment not web itself
'Workbooks("Macro for Processing Bankline
Balances.xls").Sheets("Sheet1").Range("A1:G39").Copy

'Application.SendKeys "%EP"
ActiveSheet.Paste

'ActiveSheet.PasteSpecial Format:="HTML", Link:=False, DisplayAsIcon:=False

Range("A1").Select
LastRowNonBlank = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
LastColNonBlank = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column

With TempWb
With .Sheets("Sheet1")
.Range(.Cells(2, 2), .Cells(LastRowNonBlank, LastColNonBlank -
1)).Copy
End With
.Sheets("Sheet2").Activate
Range("A1").PasteSpecial Paste:=xlValues
NewLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
NewLastCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
With .Sheets("Sheet2")


.Range(Cells(1, 1), Cells(NewLastRow, NewLastCol)).Sort
Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom


iColWithDate = WorksheetFunction.Match("Date", .Range("1:1"), 0)
sTitleString = "Bankline Balances for " & Format(.Cells(2,
iColWithDate), "dddd d mmmm yyyy")
.Range(.Cells(2, iColWithDate), .Cells(NewLastRow,
iColWithDate)).NumberFormat = sDateFormat



'.Range(.Cells(1, NewLastCol - 1), .Cells(NewLastRow,
NewLastCol)).Select
.Range(.Cells(1, 4), .Cells(NewLastRow, NewLastCol)).Select 'new 7
Mar 06
.Cells(1, NewLastCol + 1).Value = "Change"

Call RemoveCr_and_Dr
' entering the change entries on RHS
.Range(.Cells(2, NewLastCol + 1), .Cells(NewLastRow, NewLastCol +
1)).FormulaR1C1 = "=+RC[-1]-RC[-2]"
'''
Call FormatData_TEMP

'entering sub-totals at bottom
.Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
1, NewLastCol + 1)).FormulaR1C1 = "=SUM(R[-" & NewLastRow & "]C:R[-1]C)"
.Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
1, NewLastCol + 1)).NumberFormat = sNumberFormat
.Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
1, NewLastCol + 1)).Font.Bold = True
.Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
1, NewLastCol + 1)).EntireRow.Insert

.Range("A1:A2").EntireRow.Insert
With .Range("A1")
.Value = sTitleString
.Font.Bold = True
.Font.Size = 14
End With
.Range("A1").Select
End With

Sheets("Sheet1").Visible = False

End With
Call Print_Default
TempWb.Saved = True

End Sub


"Tim Williams" <saxifrax at pacbell dot net> wrote in message
news:#koCcUnQGHA.1556@TK2MSFTNGP09.phx.gbl...
> Tim,
>
> What does your code look like? If you can get the text from the clipboard
> then you might be able to extract the date and format it as you want....
>
> Tim
>
>
> "Tim Childs" <tsnip1@yahoo.co.uk> wrote in message
> news:e1I5CqhQGHA.1556@TK2MSFTNGP09.phx.gbl...
> > Hi from Europe!
> >
> > I am using VBA to paste a clipboard filled with figures and dates from a
> > Web
> > page but the VBA is altering 7 March 2006 (07/03/06) to 3 July 2006
> > (03/07/2006). This only happens in VB code - when pasting manually the
> > date
> > is pasted fine.
> >
> > I have searched the newsgroups but the only solution seems to be to use
> > Excel 2002 which I don't have.
> >
> > The date is always in the third column of the clipboard - is it possible
> > to
> > interrogate the clipboard? Are there any other avenues to explore?
> >
> > Thanks in advance
> >
> > Tim
> >
> >
>
>

Tim,

A lot of code so I admit I didn't take the time to figure it all out. A few
questions though...

Have you tried formatting the paste area as text before pasting the copied
text?
What happened when you pasted as HTML?
Is the format of the page you're copying from consistent? Are you copying
an entire table's contents and is that table in a consistent place on the
page? If so, you might be better off using IE automation and the HTML
document object model to read the data directly off the web page.

If you're still having problems feel free to e-mail me (tim j williams at
gmail dot com: no spaces). I can't promise immediate attention, but I might
be able to help given more details of what you're attempting....


Cheers
Tim.


"Tim Childs" <tsnip1@yahoo.co.uk> wrote in message
news:OgmslSuQGHA.2436@TK2MSFTNGP11.phx.gbl...
> Hi
>
> Thanks for responding.
>
> I have copied the code for the proc. below. You can see what I have tried
> on
> the pasting and sendkeys front but it did not get me anywhere ( paradigm
> for
> life?!!).
>
> The ability to do the interrogation of the clipboard would be helpful
>
> Any suggestions welcome
>
> Tim
>
>
> Sub ReformatBanklineBalanceTable()
> '
> 'Need to remove the currency option of 4 d.p. from Autoformat
> 'module (in proc.FormatData_VISIBLE) - TPC, 7 March 2006
>
> ' Macro recorded 01/03/2006 by tchilds
> '
> 'NOTE: other users may export more columns of balances than me
> '
> Dim sTitleString As String ' need to add at end of macro
> Dim NewLastCol
> Dim NewLastRow
>
> Dim LastRowNonBlank As Long 'Last Row with content
> Dim LastColNonBlank As Integer 'Last column with content - cannot exceed
> 256
>
> Dim iColWithDate
> Dim TempWb As Object
>
> Dim sDateFormat As String
> Dim sNumberFormat As String
> Dim CurrWkBk As Object
>
> Application.ScreenUpdating = False
>
> bSkipAlertForFormula = True 'stops an alert coming up warning about cells
> with
> 'formulae - not required out of P'soft Query download context
>
> sDateFormat = "d-mmm-yy"
> sNumberFormat = "#,##0.00_);[Red]-#,##0.00_)"
>
> Set CurrWkBk = ActiveWorkbook
>
> Call Workbook_Add
>
> Set TempWb = ActiveWorkbook
> CurrWkBk.ActiveSheet.Cells.Copy
>
> 'NOTE WELL: need to fill in clipboard when it was filled within Excel
> environment not web itself
> 'Workbooks("Macro for Processing Bankline
> Balances.xls").Sheets("Sheet1").Range("A1:G39").Copy
>
> 'Application.SendKeys "%EP"
> ActiveSheet.Paste
>
> 'ActiveSheet.PasteSpecial Format:="HTML", Link:=False,
> DisplayAsIcon:=False
>
> Range("A1").Select
> LastRowNonBlank = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
> LastColNonBlank = Cells.Find("*", [A1], , , xlByColumns,
> xlPrevious).Column
>
> With TempWb
> With .Sheets("Sheet1")
> .Range(.Cells(2, 2), .Cells(LastRowNonBlank, LastColNonBlank -
> 1)).Copy
> End With
> .Sheets("Sheet2").Activate
> Range("A1").PasteSpecial Paste:=xlValues
> NewLastRow = Cells.Find("*", [A1], , , xlByRows, xlPrevious).Row
> NewLastCol = Cells.Find("*", [A1], , , xlByColumns, xlPrevious).Column
> With .Sheets("Sheet2")
>
>
> .Range(Cells(1, 1), Cells(NewLastRow, NewLastCol)).Sort
> Key1:=Range("A4"), Order1:=xlAscending, Header:=xlGuess, _
> OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
>
>
> iColWithDate = WorksheetFunction.Match("Date", .Range("1:1"), 0)
> sTitleString = "Bankline Balances for " & Format(.Cells(2,
> iColWithDate), "dddd d mmmm yyyy")
> .Range(.Cells(2, iColWithDate), .Cells(NewLastRow,
> iColWithDate)).NumberFormat = sDateFormat
>
>
>
> '.Range(.Cells(1, NewLastCol - 1), .Cells(NewLastRow,
> NewLastCol)).Select
> .Range(.Cells(1, 4), .Cells(NewLastRow, NewLastCol)).Select 'new 7
> Mar 06
> .Cells(1, NewLastCol + 1).Value = "Change"
>
> Call RemoveCr_and_Dr
> ' entering the change entries on RHS
> .Range(.Cells(2, NewLastCol + 1), .Cells(NewLastRow, NewLastCol +
> 1)).FormulaR1C1 = "=+RC[-1]-RC[-2]"
> '''
> Call FormatData_TEMP
>
> 'entering sub-totals at bottom
> .Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
> 1, NewLastCol + 1)).FormulaR1C1 = "=SUM(R[-" & NewLastRow & "]C:R[-1]C)"
> .Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
> 1, NewLastCol + 1)).NumberFormat = sNumberFormat
> .Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
> 1, NewLastCol + 1)).Font.Bold = True
> .Range(.Cells(NewLastRow + 1, NewLastCol - 1), .Cells(NewLastRow +
> 1, NewLastCol + 1)).EntireRow.Insert
>
> .Range("A1:A2").EntireRow.Insert
> With .Range("A1")
> .Value = sTitleString
> .Font.Bold = True
> .Font.Size = 14
> End With
> .Range("A1").Select
> End With
>
> Sheets("Sheet1").Visible = False
>
> End With
> Call Print_Default
> TempWb.Saved = True
>
> End Sub
>
>
> "Tim Williams" <saxifrax at pacbell dot net> wrote in message
> news:#koCcUnQGHA.1556@TK2MSFTNGP09.phx.gbl...
>> Tim,
>>
>> What does your code look like? If you can get the text from the
>> clipboard
>> then you might be able to extract the date and format it as you want....
>>
>> Tim
>>
>>
>> "Tim Childs" <tsnip1@yahoo.co.uk> wrote in message
>> news:e1I5CqhQGHA.1556@TK2MSFTNGP09.phx.gbl...
>> > Hi from Europe!
>> >
>> > I am using VBA to paste a clipboard filled with figures and dates from
>> > a
>> > Web
>> > page but the VBA is altering 7 March 2006 (07/03/06) to 3 July 2006
>> > (03/07/2006). This only happens in VB code - when pasting manually the
>> > date
>> > is pasted fine.
>> >
>> > I have searched the newsgroups but the only solution seems to be to use
>> > Excel 2002 which I don't have.
>> >
>> > The date is always in the third column of the clipboard - is it
>> > possible
>> > to
>> > interrogate the clipboard? Are there any other avenues to explore?
>> >
>> > Thanks in advance
>> >
>> > Tim
>> >
>> >
>>
>>
>
>

Tim

I did try out the pasting into text-formated cells but it did not do
the trick.

I intend though to use the idea it gave of pasting the clipboard as
text itself and then interrogating the result to find the "true" date

Thanks very much for your help

Tim



Tim Williams wrote:
> Tim,
>
> A lot of code so I admit I didn't take the time to figure it all out. A few
> questions though...
>
> Have you tried formatting the paste area as text before pasting the copied
> text?
> What happened when you pasted as HTML?
> Is the format of the page you're copying from consistent? Are you copying
> an entire table's contents and is that table in a consistent place on the
> page? If so, you might be better off using IE automation and the HTML
> document object model to read the data directly off the web page.
>
> If you're still having problems feel free to e-mail me (tim j williams at
> gmail dot com: no spaces). I can't promise immediate attention, but I might
> be able to help given more details of what you're attempting....
>
>
> Cheers
> Tim.
>
>
> "Tim Childs" <tsnip1@yahoo.co.uk> wrote in message
> news:OgmslSuQGHA.2436@TK2MSFTNGP11.phx.gbl...