barbosajulio77
08-17-2021, 01:46 AM
Hi,
have the code below when importing csv file the invoice # with 12 digits get imported like this "4.00003E+11
" when the number should imported like this "400003395647" please help to modify the code that import the correct number.
here is the code:
Sub ImportRawData()
Dim c As Long
Dim Col As Variant
Dim Filename As String
Dim Filepath As Variant
Dim rngBeg As Range
Dim rngEnd As Range
Dim rngDst As Range
Dim rngSrc As Range
Dim rowsize As Long
Dim wkbDst As Workbook
Dim wkbSrc As Workbook
Dim vFile
vFile = Application.GetOpenFilename("CSV Files(*.csv),*.csv", , "please select a file", MultiSelect:=False)
Set wkbDst = ThisWorkbook
Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:E17")
Filepath = "C:\Users\jose.rossi\Desktop\AP_NCL_VENDOR STATEMENT.xlsm"
Filename = "apvtrn.csv"
On Error Resume Next
Set wkbSrc = Workbooks(Filename)
If Err = 9 Then
If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
'Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
If Filename = "False" Then Exit Sub
Set wkbSrc = Workbooks.Open(Filename)
End If
On Error GoTo 0
' Clear previous data.
'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count) .ClearContents
' Import the data.
With wkbSrc.Worksheets("apvtrn").UsedRange
' Step through the source data columns.
For Each Col In Array("DM", "DI", "DN", "DP", "DQ")
' Data starts on row 1.
Set rngBeg = .Parent.Cells(1, Col)
' Find the row where the data ends in this column.
Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
' Number of rows in this column.
rowsize = rngEnd.Row - rngBeg.Row
If rowsize > 0 Then
Set rngSrc = .Parent.Range(rngBeg, rngEnd)
rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
End If
' Increment the column offset.
c = c + 1
Next Col
End With
End Sub
Thank you,
have the code below when importing csv file the invoice # with 12 digits get imported like this "4.00003E+11
" when the number should imported like this "400003395647" please help to modify the code that import the correct number.
here is the code:
Sub ImportRawData()
Dim c As Long
Dim Col As Variant
Dim Filename As String
Dim Filepath As Variant
Dim rngBeg As Range
Dim rngEnd As Range
Dim rngDst As Range
Dim rngSrc As Range
Dim rowsize As Long
Dim wkbDst As Workbook
Dim wkbSrc As Workbook
Dim vFile
vFile = Application.GetOpenFilename("CSV Files(*.csv),*.csv", , "please select a file", MultiSelect:=False)
Set wkbDst = ThisWorkbook
Set rngDst = wkbDst.Worksheets("STATEMENT").Range("A17:E17")
Filepath = "C:\Users\jose.rossi\Desktop\AP_NCL_VENDOR STATEMENT.xlsm"
Filename = "apvtrn.csv"
On Error Resume Next
Set wkbSrc = Workbooks(Filename)
If Err = 9 Then
If Filepath <> "" Then ChDir Filepath Else ChDir ThisWorkbook.Path
'Filename = Application.GetOpenFilename("Excel Workbooks, *.xlsx")
If Filename = "False" Then Exit Sub
Set wkbSrc = Workbooks.Open(Filename)
End If
On Error GoTo 0
' Clear previous data.
'rngDst.Resize(rngDst.Parent.UsedRange.Rows.Count) .ClearContents
' Import the data.
With wkbSrc.Worksheets("apvtrn").UsedRange
' Step through the source data columns.
For Each Col In Array("DM", "DI", "DN", "DP", "DQ")
' Data starts on row 1.
Set rngBeg = .Parent.Cells(1, Col)
' Find the row where the data ends in this column.
Set rngEnd = .Parent.Cells(Rows.Count, Col).End(xlUp)
' Number of rows in this column.
rowsize = rngEnd.Row - rngBeg.Row
If rowsize > 0 Then
Set rngSrc = .Parent.Range(rngBeg, rngEnd)
rngDst.Offset(0, c).Resize(rowsize, 1).Value = rngSrc.Value
End If
' Increment the column offset.
c = c + 1
Next Col
End With
End Sub
Thank you,