数据导入时重叠内容

我正在使用Excel VBA进行项目，但是导入数据时遇到问题。每次导入数据时，它都会与现有列重叠。

有人可以帮我解决这个问题，下面是代码。

Private Sub CommandButton1_Click()

Dim myFile As String, text As String, textline As String, Name As Integer, Phone As Integer, Address1 As Integer, Dated As Integer
Dim Email As Integer, Postcode As Integer, SR As Integer, MTM As Integer, Serial As Integer, Problem As Integer, Action As Integer


myFile = "C:\Users\test.txt"


Open myFile For Input As #1
    Do Until EOF(1)
        Line Input #1, textline
        text = text & textline
    Loop
Close #1

Name = InStr(text, "Name")
Phone = InStr(text, "Phone")
Address1 = InStr(text, "Address1")
Email = InStr(text, "Email")
Postcode = InStr(text, "Postcode")
SR = InStr(text, "SR")
MTM = InStr(text, "MTM")
Serial = InStr(text, "Serial")
Problem = InStr(text, "Problem")
Action = InStr(text, "Action")
Dated = InStr(text, "Dated")


Range("C11").Value = Mid(text, Name + 6, 15)
Range("H13").Value = Mid(text, Phone + 6, 8)
Range("C15").Value = Mid(text, Address1 + 9, 25)
Range("C13").Value = Mid(text, Email + 6, 15)
Range("H16").Value = Mid(text, Postcode + 9, 5)
Range("C10").Value = Mid(text, SR + 4, 8)
Range("H14").Value = Mid(text, MTM + 5, 8)
Range("H15").Value = Mid(text, Serial + 8, 9)
Range("C17").Value = Mid(text, Problem + 9, 15)
Range("C18").Value = Mid(text, Action + 7, 10)
Range("H10").Value = Mid(text, Dated + 7, 10)

End Sub

在所附工作表中查找数据。手机跳到name列，这同样适用于其他列。H13中的数据也将传入C11。

。

编辑

保罗，您好，我在打印工作表并将其转换为PDF时仍然遇到问题。

不使用您的第一或第二个代码。我可以将以下代码运行到工作表中并转换为PDF，但是现在当您运行第一和第二个代码后，以下代码无法将工作表转换为PDF ...我一直在获取“应用程序定义的错误或对象定义的错误”和运行时错误“ 1004”文档未保存。该文档可能已打开，或者在保存时可能遇到错误。

我可以知道我的代码有什么问题吗？

Private Sub CommandButton2_Click()
    Dim FilePath As String
    Dim FileName As String
    Dim MyDate As String
    Dim report As String
    Dim Name As String

    FilePath = "C:\Users\Documents\test\"
    MyDate = Format(Date, " - MM-DD-YYYY")
    report = " - Quatation"
    Name = Worksheets("Sheet1").Range("C10")

    Sheets("Sheet1").Range("A1:I60").ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=FilePath & Name & MyDate & report
End Sub

Private Sub report()
    Dim myFile As String, lastRow As Long
    myFile = "C:\Users\Documents\test\" & Sheets("Sheet1").Range("C11") & "_" & Sheets("Sheet1").Range("C17") & Format(Now(), "yyyy-mm-dd") & ".pdf"
    lastRow = Sheets("Sheet3").UsedRange.SpecialCells(xlCellTypeLastCell).Row + 1
    'Transfer data to sheet3
    Sheets("Sheet3").Cells(lastRow, 1) = Sheets("Sheet1").Range("C11")
    Sheets("Sheet3").Cells(lastRow, 2) = Sheets("Sheet1").Range("C17")
    Sheets("Sheet3").Cells(lastRow, 3) = Sheets("sheet1").Range("I28")
    Sheets("Sheet3").Cells(lastRow, 4) = Now
    Sheets("Sheet3").Hyperlinks.Add Anchor:=Sheets("Sheet3").Cells(lastRow, 5), Address:=myFile, TextToDisplay:=myFile
    'Create invoice in PDF format
    Sheets("sheet1").ExportAsFixedFormat Type:=xlTypePDF, FileName:=myFile
    Application.DisplayAlerts = False
    'create invoice in XLSX format
    ActiveWorkbook.SaveAs "C:\Users\Documents\test\" & Sheets("Sheet1").Range("C11") & "_" & Sheets("Sheet1").Range("C17") & "_" & Format(Now(), "yyyy-mm-dd") & ".xlsx", FileFormat:=51
    'ActiveWorkbook.Close
    Application.DisplayAlerts = True
End Sub