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 said:
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
