November 16, 2019
I have a problem with one of the macros I've recently created.
Every morning I get an excel file (.xls) with some data. From the main excel file, called Deljit_Renault.... I import the data of the first file. The problem is that in this data, there are some numbers that have more than three figures and when this is imported, they are not copy as it should. For instance, if the original number is 1200 it is copy as 1,2. Could anyone help me to solve this problem?
Dim OpenBook As Workbook
Dim FileToOpen As Variant
Dim LastRow As Long
Dim LastRowTemp As Long
Dim Hoja1 As Worksheet
Dim PT_Deljit As PivotTable
Dim PT_Fecha As PivotTable
FileToOpen = Application.GetOpenFilename(Filefilter:="Excel files (*.xls),*.xls", Title:="Select Workbook to Import")
If FileToOpen <> False Then
Set OpenBook = Application.Workbooks.Open(FileToOpen)
'Locate last empty row in temporary file
LastRowTemp = Range("B" & Rows.Count).End(xlUp).Row
'copy the info in the openbook
OpenBook.Sheets(1).Range("B5:x" & LastRowTemp).Copy
'Locate last empty row in Deljit file
LastRow = ThisWorkbook.Sheets("PegarAquiLaJitK").Range("B" & Rows.Count).End(xlUp).Row + 2
ThisWorkbook.Worksheets("PegarAquiLaJitK").Range("B" & LastRow).PasteSpecial Paste:=xlPasteValues
Set PT_Deljit = ActiveSheet.PivotTables("Tb_Deljit")
Set PT_Fecha = ActiveSheet.PivotTables("Tb_Fecha")
I also attach the two files.
Thanks a lot,
December 20, 2019
i assume at some point the data was in a csv file?
i would check how the file is being imported and see if it is being converted due to the comma delimiter.
if you still cant get it to work, as a nasty work around you could run a loop on that column like the below
Dim xCol As Range
Dim xCell As Range
Set xCol = Range("l:l")
For Each xCell In xCol
If InStr(xCell.Value, ".") > 0 Then
xCell = xCell.Value * 1000
November 16, 2019
I thought the worksheets were uploaded... yes, you are right, I get the file to be imported from SAP and it is not an .xlsm.
Anyway, your solution works perfect. Thanks so much.
PD: I am trying to upload the files just in case it is of any help for anyone else.