PDA

View Full Version : when importing data VBA code changes values ​​of the original ​​book



Ghuphuneho
02-05-2020, 12:30 PM
Hi,

i have a problem and i dont know how resolve it

i need to transfer a info from several excel files to one. My VBA code works but it changes some number values. for example, if a cell values is 89,000 the code transforme the value to 89.000. but not all, just some

Please i need your help.

i have 4 sheets, and each one has an associated macro. Then with a button I call them one by one

The 4 codes are more or less the same, I leave one as a reference. I leave attached the files.

Thak you very much!!!!:crying::crying:




Sub MB25D()

Application.ScreenUpdating = False
Dim i As Integer

Worksheets.Add.Name = "MB25D"

Call ContarArchivosZI

Application.ScreenUpdating = False
Dim WorkBookOrigen As Workbook
Dim wsOrigen As Excel.Worksheet, _
wsDestino As Excel.Worksheet, _
rngOrigen As Excel.Range, _
rngDestino As Excel.Range, _
NombreArchivo As String, _
carpeta As String
Dim CNT As Integer


carpeta = ActiveWorkbook.Path & "\"

nArchivo = 1


NombreArchivo = Dir(carpeta & "MB25" & "*.MH*")


Do While Len(NombreArchivo) > 0



Set WorkBookOrigen = Workbooks.Open(carpeta & NombreArchivo)

NombreArchivo = Dir()

ThisWorkbook.Activate

Set wsOrigen = WorkBookOrigen.Worksheets(1)
Set wsDestino = Worksheets("MB25D")

Const celdaOrigen = "A1"

Set rngOrigen = wsOrigen.Range(celdaOrigen)

wsOrigen.Activate
rngOrigen.Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy

Errores:
If Err.Number = 1004 Then
wsOrigen.Activate
rngOrigen.Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
End If

For n = 1 To 1
wsDestino.Activate

'On Error GoTo Errores

Sheets("MB25D").Select

wsDestino.Cells(Columns.Count, 2).End(xlUp).Offset(0, -1).PasteSpecial

Next n
Application.CutCopyMode = False

WorkBookOrigen.Save


nArchivo = nArchivo + 1


Call Progreso


Loop

j = nArchivo - 1

Sheets("MB25").Select
i = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("MB25").Range("A1:H" & i + 1).Clear
Range("A1").Select
ActiveCell.FormulaR1C1 = "=+MB25D!RC"
Range("A1").Select
Selection.AutoFill Destination:=Range("A1:H1"), Type:=xlFillDefault
Range("A1:AJ1").Select
Range("AJ1").Select

Sheets("MB25D").Select

i = Cells(Rows.Count, 1).End(xlUp).Row

Sheets("MB25").Select

Range("A1:H1").Select
Selection.AutoFill Destination:=Range("A1:H" & i)

Range("I2:X2").Select
Selection.AutoFill Destination:=Range("I2:X" & i)

Sheets("MB25").Select

i = Cells(Rows.Count, 1).End(xlUp).Row

Range("A1:H" & i).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Range("A2322").Select
Selection.End(xlUp).Select
ActiveWindow.SmallScroll Down:=0
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.DisplayAlerts = False

Sheets("MB25D").Delete
Application.DisplayAlerts = True


Dim celda As Range
Application.StatusBar = "Convirtiendo celdas seleccionadas a formato de número..."

i = Cells(Rows.Count, 1).End(xlUp).Row




Range("H2:H" & i).Select
Selection.NumberFormat = "m/d/yyyy"

Range("B2:E" & i).Select
Selection.NumberFormat = "0"

Range("B2:E" & i).Select
Selection.NumberFormat = "0"
Range("G2:G" & i).Select
Selection.NumberFormat = "0"

Sheets("MB25").Select
Dim r As Integer
Dim f As Integer

f = 2
r = 2

i = Cells(Rows.Count, 1).End(xlUp).Row




Do While r < i


Range("B" & r).Select
Range("B" & f).Select

If Range("B" & r) = "" Then

Range("B" & r).Value = 0

Else

Range("B" & r) = Range("B" & r).Value * 1
Range("B" & r).Interior.Color = RGB(150, 160, 26)

End If



r = r + 1

Loop


Dim palabra, extension


extension = 1


f = 2
r = 2

i = Cells(Rows.Count, 1).End(xlUp).Row


Do While r < i
palabra = Range("E" & r)
Range("y" & r) = Left(palabra, extension)


Range("E" & r).Select
Range("E" & f).Select

If Range("Y" & r) = "S" Then


Else

Range("E" & r) = Range("E" & r).Value * 1
Range("E" & r).Interior.Color = RGB(150, 160, 26)

End If



r = r + 1

Loop

Range("B2:E" & i).Select





Application.StatusBar = False


Application.ScreenUpdating = True


End Sub








Public Sub Progreso()
Dim contador As Integer
Dim Maximo As Integer
Dim Mitiempo As Double



Maximo = nArchivo - 1

For contador = 1 To Maximo Step 1
Mitiempo = Timer
Do
Loop While Timer - Mitiempo < 0.02
Application.StatusBar = "Progreso: " & Maximo & _
" de " & i & " (" & Format(Maximo / i, "Percent") & ")"
DoEvents
Next contador

Application.StatusBar = False
End Sub









Public Sub ContarArchivosMB25()
Dim cNombreArchivo, cCarpeta As String


cCarpeta = ActiveWorkbook.Path & "\"

Conteo = 1
25926
cNombreArchivo = Dir(cCarpeta & "MB25" & "*.MH*")

Do While Len(cNombreArchivo) > 0
cNombreArchivo = Dir()
Conteo = Conteo + 1
Loop

i = Conteo - 1
End Sub

p45cal
02-07-2020, 05:45 AM
The data in the mhtml files is already a mix of numbers and non numbers in a given column.
Your code (unnecessarily) saves the .mhtml files after opening them which it doesn't need to do.
Can you attach an MHTML file that has NEVER even been opened in Excel (or by anything else for that matter) nor processed by your file?
Preferably as you receive it, unopened. I'd like to examine it.

Separately:
Is there a chance you can obtain the information in another form?

The problem may be that Excel tries to interpret these numbers but interprets some of them wrongly, especially as this file appears to be from a locale which uses a comma as a decimal separator and a dot as the thousands separator.

Ghuphuneho
02-07-2020, 08:38 AM
The data in the mhtml files is already a mix of numbers and non numbers in a given column.
Your code (unnecessarily) saves the .mhtml files after opening them which it doesn't need to do.
Can you attach an MHTML file that has NEVER even been opened in Excel (or by anything else for that matter) nor processed by your file?
Preferably as you receive it, unopened. I'd like to examine it.

Separately:
Is there a chance you can obtain the information in another form?

The problem may be that Excel tries to interpret these numbers but interprets some of them wrongly, especially as this file appears to be from a locale which uses a comma as a decimal separator and a dot as the thousands separator.


Thak you for your answer.
i can obtain a XLS file, but the problem is the same. the info is in SAP.

the problem like you say, is the decimal separator at the end. the number 100,00 or 1.000.000,000 VBA transforms it to 10.000 and 1.000.000.000

I thought I had attached them. I attach the files.25942
I thought I had attached them. I attach the files

p45cal
02-07-2020, 09:21 AM
You did attach the files (they appeared in the middle of your last snippet of code) but they've all got evidence of having been through Excel, so I think the damage has already been done.
How are these mhtml files created?

snb
02-07-2020, 10:38 AM
@P45cal

I don't think so.

Open the file in a Text editor; replace ",000</td>" by ",00</td>" and the 'problem' is solved.

p45cal
02-07-2020, 11:10 AM
@snb. I tried that search/replace in Notepad, but still column S of ZICO.mhtml opened in Excel is awry (cell S7 v. cell S8) - though could this be because my locale is the UK?:

25943

snb
02-07-2020, 01:06 PM
Is this more to your liking ?


Sub M_snb()
c00 = "G:\OF\zico.Mhtml"

With CreateObject("scripting.filesystemobject")
c01 = .opentextfile(c00).readall

For j = 0 To 99
c01 = Replace(c01, Format(j, ".00") & "0</td>", Format(j, ",00") & "</td>")
Next

.createtextfile(c00).write c01
End With
End Sub

Paul_Hossler
02-07-2020, 01:57 PM
SAP exports reports as Excel files in a MIME wrapper



MIME-Version: 1.0
X-Document-Type: Workbook
Content-Type: multipart/related; boundary="----=_NextPart_01D5DC39.6D74B7B0"


Este documento es una página web de un solo archivo, también conocido como "archivo de almacenamiento web". Si está viendo este mensaje, su explorador o editor no admite archivos de almacenamiento web. Descargue un explorador que admita este tipo de archivos.


------=_NextPart_01D5DC39.6D74B7B0
Content-Location: file:///C:/267E2CAF/ZICO.htm
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html; charset="us-ascii"


<html xmlns:v=3D"urn:schemas-microsoft-com:vml"
xmlns:o=3D"urn:schemas-microsoft-com:office:office"
xmlns:x=3D"urn:schemas-microsoft-com:office:excel"
xmlns=3D"http://www.w3.org/TR/REC-html40">


<head>
<meta name=3D"Excel Workbook Frameset">
<meta http-equiv=3DContent-Type content=3D"text/html; charset=3Dus-ascii">
<meta name=3DProgId content=3DExcel.Sheet>
<meta name=3DGenerator content=3D"Microsoft Excel 15">
<link rel=3DFile-List href=3D"ZICO_archivos/filelist.xml">
<![if !supportTabStrip]>
<link id=3D"shLink" href=3D"ZICO_archivos/sheet001.htm">
<link id=3D"shLink" href=3D"ZICO_archivos/sheet002.htm">
<link id=3D"shLink" href=3D"ZICO_archivos/sheet003.htm">




The 4 MHTML files can be opened with Excel and the formats seem consistent, but a number is a string, so after opening a MHTML I ran a macro to make text 'numbers' into real numbers, and text 'dates' into real dates

25948

25946


If you copy/paste then it really gets messed up since Excel tries to be helpful

It's really not necessary to start replacing tags, etc. in the HTML

What I'd do is

1. have my 30122019-Torre de Control MyR CE03 prueba.xlsb workbook open each MHTML

2. Copy the Worksheet (not copy/paste)

3. run a format/cleanup macro on that sheet to fix 'numbers' and 'dates' and delete unneeded rows and columns

4. use the data after cleaned and formatted


I used to have a macro laying around that takes a SAP report and cleaned it up. Same format as yours

a. Save B2, B3, and B4 values
b. deleted rows 1-5
c. deleted columns I didn't want
d. deleted rows (e.g. subtotals and totals) I didn't want
e. made columns with text numbers into real numbers, same for dates

p45cal
02-07-2020, 06:01 PM
In each of the 4 MHTML files there are about 3 instances of:

{mso-displayed-decimal-separator:"\.";
mso-displayed-thousand-separator:"\,";}
which if all changed to:

{mso-displayed-decimal-separator:"\,";
mso-displayed-thousand-separator:"\.";}
seems to put things right.
I attach them here for you to check.
If OK you can either arrange to change how SAP exports these files or we could incorporate the tweak to the files in the macros.