Copy Particular Text to Excel File...

[nitmac]

Hi There,

I have a backup report which is having 20 number of clients and many server related to clients.

Now the problem is i'm receiving backup log files by mails and doing the copy past for the required material. Please have a look at sample files.

I have recorded the Macro for the method but when the log file contains the error the macro i have recorded pasting the junk value's and it is because of errors which is adding the rows for error messages.

Could you please help me out for the issue. Is that can be automated by using the VBA?

Please help me out for this becuase i'm speding 3-4 hours of my time for completing the report.

Many Thanks,
Mac

[parttime_guy]

Yo! Nitmac,

I have tried to solve ur problem manually, plz have a look at the attached file and let me have ur views.

Regards

[nitmac]

Hi,

Thanks alot for looking into the problem... The file which you had sent me cannot solve my problem i want something automatic which can copy the data into particular cell's where i wanted...

I have created Macro for copying the cells but when i will get error messages in log file... that macro is not usable at that time...

Please help me out for that...

Cheers!
Mac

[mdmackillop]

Hi Mac,
Can you post your macro?

[nitmac]

Hi,
Please check the following code i have written to copy the data but when the log file contains the error this is not useful for me... and i have to copy the data manually... Also i'm attaching the result.xls file as well as the log file from which i have taken the data...

Macro copying procedure:
1. Open file into comma delimited format in MS-Excel
2. Run the macro which is written to copy the file

And the rows should be more than 10000.

Please helpme out as i'm trying to resolve it from last 2 months.

Cheers!
Mac



[VBA]
Sub GHG()
'
' GHG Macro
' Macro recorded 23/03/2007 by NMachh
'
'
Dim Response As Integer
Response = MsgBox(prompt:="GHG GFS backup log file should be opened in background...", Title:="Please Confirm...", Buttons:=vbOKCancel)
If Response = vbOK Then
Windows("GHG.LOG").Activate
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F123").Select
Windows("GHG.LOG").Activate
Range("D18").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F124").Select
Windows("GHG.LOG").Activate
Range("D36").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F125").Select
Windows("GHG.LOG").Activate
Range("D54").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
Range("F127").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F125").Select
ActiveCell.FormulaR1C1 = "???"
Range("F126").Select
ActiveCell.FormulaR1C1 = "???"
Range("F129").Select
Windows("GHG.LOG").Activate
Range("D115").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F130").Select
Windows("GHG.LOG").Activate
Range("D133").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F131").Select
Windows("GHG.LOG").Activate
Range("D151").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F132").Select
Windows("GHG.LOG").Activate
Range("D169").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F133").Select
Windows("GHG.LOG").Activate
Range("D187").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F135").Select
Windows("GHG.LOG").Activate
Range("D248").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F136").Select
Windows("GHG.LOG").Activate
Range("D266").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F137").Select
Windows("GHG.LOG").Activate
Range("D284").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F138").Select
Windows("GHG.LOG").Activate
Range("D302").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
Range("F140").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F138").Select
ActiveCell.FormulaR1C1 = "???"
Range("F139").Select
ActiveCell.FormulaR1C1 = "???"
Range("F142").Select
Windows("GHG.LOG").Activate
Range("D363").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F143").Select
Windows("GHG.LOG").Activate
Range("D381").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F144").Select
Windows("GHG.LOG").Activate
Range("D399").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F145").Select
Windows("GHG.LOG").Activate
Range("D417").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
Range("F146").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F145").Select
ActiveCell.FormulaR1C1 = "???"
Range("F147").Select
Windows("GHG.LOG").Activate
Range("D435").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F149").Select
Windows("GHG.LOG").Activate
Range("D496").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F151").Select
Windows("GHG.LOG").Activate
Range("D502").Select
Windows("AMS_Bkp.xls").Activate
Windows("GHG.LOG").Activate
Range("D526").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F152").Select
Windows("GHG.LOG").Activate
Range("D544").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F153").Select
Windows("GHG.LOG").Activate
Range("D562").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F155").Select
Windows("GHG.LOG").Activate
Range("D623").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F156").Select
Windows("GHG.LOG").Activate
Range("D641").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F157").Select
Windows("GHG.LOG").Activate
Range("D659").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F159").Select
Windows("GHG.LOG").Activate
Range("D720").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F160").Select
Windows("GHG.LOG").Activate
Range("D738").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F161").Select
Windows("GHG.LOG").Activate
Range("D756").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F163").Select
Windows("GHG.LOG").Activate
Range("D817").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F164").Select
Windows("GHG.LOG").Activate
Range("D835").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F165").Select
Windows("GHG.LOG").Activate
Range("D853").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F167").Select
Windows("GHG.LOG").Activate
Range("D914").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F168").Select
Windows("GHG.LOG").Activate
Range("D932").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F169").Select
Windows("GHG.LOG").Activate
Range("D950").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F171").Select
Windows("GHG.LOG").Activate
Range("D1011").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F172").Select
Windows("GHG.LOG").Activate
Range("D1029").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F173").Select
Windows("GHG.LOG").Activate
Range("D1047").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F116").Select
Windows("GHG.LOG").Activate
Range("D1114").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F117").Select
Windows("GHG.LOG").Activate
Range("D1115").Select
Selection.Copy
Windows("AMS_Bkp.xls").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
Range("F118").Select
ActiveCell.FormulaR1C1 = "0"
Range("F119").Select
ActiveCell.FormulaR1C1 = "0"
Range("F120").Select
ActiveCell.FormulaR1C1 = "Y"
Range("F121").Select
ActiveCell.FormulaR1C1 = "Y"
Range("F115:F174").Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
With Selection.Font
.Name = "Arial"
.Size = 8
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
ActiveWindow.SmallScroll Down:=-63
Range("F115").Select
Else
End If
End Sub
[/VBA]