Hello,
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?
Sub ImportData()
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
Call Entry_Point
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
OpenBook.Close
End If
ThisWorkbook.RefreshAll
ThisWorkbook.Sheets("Precarga").Select
Call Exit_Point
Set PT_Deljit = ActiveSheet.PivotTables("Tb_Deljit")
Set PT_Fecha = ActiveSheet.PivotTables("Tb_Fecha")
PT_Fecha.PivotCache.Refresh
PT_Deljit.PivotCache.Refresh
End Sub
I also attach the two files.
Thanks a lot,
Cristina
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
Sub FixThousands()
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
End If
Next xCell
End Sub
Hello Purfleet,
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.
Best,
Cristina
PD: I am trying to upload the files just in case it is of any help for anyone else.
