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" wrote in message
...
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").Co py

'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
...
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" wrote in message
...
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" wrote in message
...
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").Co py

'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
...
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" wrote in message
...
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" wrote in message
...