Блог

Я хочу скопировать несколько текстов с одного листа на другой. Например: 01/02/2021 . Однако VBA автоматически преобразует его в 2020/01/02 . Как я могу это остановить? Следующие коды не сработали.

Пример1:

 sheet_1.Range("A1:A" amp; sheet1.Cells(1, 1).CurrentRegion.End(xlDown).row).Copy
ws.Range("start").PasteSpecial xlPasteValues 
ws.Range("start").PasteSpecial xlPasteFormats
 

Пример2:

 sheet_1.Range("A1:A" amp; sheet1.Cells(1, 1).CurrentRegion.End(xlDown).row).Copy
    ws.Range("start").PasteSpecial xlPasteFormulasAndNumberFormats
 

Пример3:

 sheet_1.Range("A1:A" amp; sheet1.Cells(1, 1).CurrentRegion.End(xlDown).row).Copy
    ws.Range("start").Paste xlPaste Format:="Text" 'This causes an error
 

Пожалуйста, попробуйте следующий код. Он извлечет дату из (псевдо) файла xls и поместит ее в первую колонку активного листа. Правильно отформатирован как дата:

 Sub openXLSAsTextExtractDate()
   Dim sh As Worksheet, arrTXT, arrLine, arrD, arrDate, fileToOpen As String, i As Long, k As Long
   
   Set sh = ActiveSheet 'use here the sheet you need
   fileToOpen = "xls file full name" 'use here the full name of the saved xls file
   'put the file content in an array splitting the read text by end of line (vbCrLf):
   arrTXT = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fileToOpen, 1).ReadAll, vbCrLf)
   ReDim arrDate(UBound(arrTXT))          'redim the array where the date will be kept, to have enough space for all the date values
   For i = 39 To UBound(arrTXT) - 1       'iterate between the array elements, starting from the row where date data starts
        arrLine = Split(arrTXT(i), vbTab) 'split the line by vbTab
        arrD = Split(arrLine(0), "/")     'split the first line element (the date) by "/"
        arrDate(k) = DateSerial(arrD(2), arrD(1), arrD(0)): k = k   1 'properely format as date and fill the arrDate elements
    Next i
    ReDim Preserve arrDate(k - 1)         'keep only the array elements keeping data
    With sh.Range("A1").Resize(UBound(arrDate)   1, 1)
        .value = Application.Transpose(arrDate)  'drop the array content
        .NumberFormat = "dd/mm/yyyy"             'format the column where the date have been dropped
    End With
End Sub
 

Отредактированный:

Ты ничего не сказал…

Итак, я сделал код, возвращающий всю таблицу (в активном листе). Пожалуйста, проверьте это. Это займет всего несколько секунд:

 Sub openXLSAsText()
   Dim sh As Worksheet, arrTXT, arrLine, arrD, arrData, fileToOpen As String, i As Long, j As Long, k As Long
   
   Set sh = ActiveSheet 'use here the sheet you need
   fileToOpen =  "xls file full name" 'use here the full name of the saved xls file
   'put the file content in an array splitting the read text by end of line (vbCrLf):
   arrTXT = Split(CreateObject("Scripting.FileSystemObject").OpenTextFile(fileToOpen, 1).ReadAll, vbCrLf)

   ReDim arrData(1 To 10, 1 To UBound(arrTXT))  'redim the array where the date will be kept, to have enough space for all the date values
   For i = 38 To UBound(arrTXT) - 1             'iterate between the array elements, starting from the row where table header starts
        arrLine = Split(arrTXT(i), vbTab)       'split the line by vbTab
        k = k   1                               'increment the k variable (which will become the table row)
        For j = 0 To 9
            If j = 0 And k > 1 Then
                arrD = Split(arrLine(j), "/")   'split the first line element (the date) by "/"
                arrData(j   1, k) = DateSerial(arrD(2), arrD(1), arrD(0)) 'propperely format as date and fill the arrDate elements
            ElseIf j = 2 Or j = 3 Then
                arrData(j   1, k) = Replace(arrLine(j), ",", ".")  'correct the format for columns 3 and four (replace comma with dot)
            Else
                 arrData(j   1, k) = arrLine(j)                    'put the rest of the column, not processed...
            End If
        Next j
    Next i
    ReDim Preserve arrData(1 To 10, 1 To k)      'keep only the array elements with data
    With sh.Range("A1").Resize(UBound(arrData, 2), UBound(arrData))
        .value = Application.Transpose(arrData)  'drop the array content
        .EntireColumn.AutoFit                    'autofit columns
        .Columns(1).NumberFormat = "dd/mm/yyyy"  'format the first column
    End With
    MsgBox "Ready..."
End Sub