basically I am currently responsible for extracting and creating reports from the system.
I receive a report in a table but as a html web page(I select save as and them html).
Next I can rename the file to report.xls which is not the best but quite efficient way to open the same table in Excel.
I’ve prepared some nice macro’s to import couple files at a time but and merge into one big report file however the report is not perfect and can’t be sorted due to some merged cells.
Mainly when I do the report the tables in HTML are shown properly however in couple of description fields there was the ENTER sign used and thus when importing into Excel it turns out to be a merged cell.
and so I receive a row of data in the format:
LINE1 and LINE2 are two separate cells because ENTER was used in the description.
I want this to be one cell in EXCEL.
Anyone got a solution or a hint what a macro should look like when importing such a file?
There are at least couple of rows like that in the report file and the rest is normal. I have around 2000 rows in a report each day and it would help me a lot to automatically remove those additional cells so 1 row would only include information about 1 entry.
Currently I am using:
Dim NoOfFiles As Integer
Dim name As String
NoOfFiles = Range(“D6”).Value
For i = 1 To NoOfFiles
name = i & “.xls”
Workbooks.Open Filename:=(“C:\daily\” & name)
Workbooks.Open Filename:=(“C:\daily\out.xls”)
On Error GoTo ErrHandler
Workbooks(“temp1.xls”).Activate
Worksheets(2).Activate
If i = 1 Then
Workbooks(name).Activate
Dim LastCell As Range
With ActiveSheet
Set LastCell = .Cells(.Rows.Count, “A”).End(xlUp)
End With
CellCount1 = LastCell.Row
Workbooks(name).Worksheets(1).Range(“a4:t” & CellCount1).Copy Workbooks(“temp1.xls”).Worksheets(2).Range(“a2”)
Workbooks(name).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close
Else
Workbooks(name).Activate
Dim LastCell3 As Range
With ActiveSheet
Set LastCell3 = .Cells(.Rows.Count, “A”).End(xlUp)
End With
CellCount1 = LastCell3.Row
Workbooks(“temp1.xls”).Activate
Dim LastCell2 As Range
Dim OstCell As Integer
With ActiveSheet
Set LastCell2 = .Cells(.Rows.Count, “A”).End(xlUp)
End With
OstCell = LastCell2.Row + 1
Workbooks(name).Worksheets(1).Range(“a4:t” & CellCount1).Copy Workbooks(“temp1.xls”).Worksheets(2).Range(“a” & OstCell)
Workbooks(name).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close
End If
Next i
Workbooks(“temp1.xls”).Activate
Worksheets(2).Activate
Dim FinalC As Range
With ActiveSheet
Set FinalC = .Cells(.Rows.Count, “A”).End(xlUp)
End With
Fin = FinalC.Row
Workbooks(“temp1.xls”).Worksheets(2).Range(“a2:t” & Fin).Copy Workbooks(“out.xls”).Worksheets(1).Range(“a5”)
Workbooks(“out.xls”).Activate
File_To_SaveAs_Name = Application.GetSaveAsFilename( _
fileFilter:=”Excel files (*.xls), *.xls”)
ActiveWorkbook.SaveAs Filename:=File_To_SaveAs_Name, CreateBackup:=False
ActiveWorkbook.Close
Workbooks(“temp1.xls”).Activate
ThisWorkbook.Saved = True
ActiveWorkbook.Close
this macro is not the best one but it does its job.
depending on cell D6 and the number in it, it imports as many files named 1.xls-n.xls, copies the proper range from those files and pastes them into my temp1.xls, if there is some data already in the first cell of temp1.xls, then it searches for the last cell with data and copies data from next file after the previous. then all data is transferred to already prepared output file and automatically saved asking for the name.
I would really appreciate help.