Excel Forum

Code: Select all

Private Sub CommandButton1_Click()
 
    Dim FileSaveName As String
    Dim LastRange As Range
    
    Set LastRange = Worksheets("Eng").Range("E65536").End(xlUp). _
        Offset(0, -4).Resize(1, 17)
        
    LastRange.Copy
    Workbooks.Add
    
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    FileSaveName = Application.GetSaveAsFilename( _
        fileFilter:="Text Files (*.txt),*.txt")
             If FileSaveName <> "" Then
                ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlText
                MsgBox "Êè§ÍÍ¡ File ä»·Õè " & FileSaveName & " áÅéǤÃѺ"
            End If
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
    
End Sub

Code: Select all

Private Sub ExportData()

    Dim FileSaveName As String
    Dim LastRange As Range
   
   Set LastRange = Worksheets("Eng").Range("E65536").End(xlUp). _
        Offset(0, -4).Resize(1, 17) 'Select last range
       
    LastRange.Copy
    Workbooks.Add
   
    Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
        xlNone, SkipBlanks:=False, Transpose:=False
        Application.CutCopyMode = False
    With ActiveSheet
    FileSaveName = "D:\" & .Range("D1") & .Range("F1") 'Drive and file name
             If FileSaveName <> "" Then
                ActiveWorkbook.SaveAs Filename:=FileSaveName, FileFormat:=xlText
                MsgBox "Êè§ÍÍ¡ File ä»·Õè " & FileSaveName & " áÅéǤÃѺ"
            End If
    End With
    Application.DisplayAlerts = False
    ActiveWindow.Close
    Application.DisplayAlerts = True
   
End Sub

Code: Select all

Private Sub CommandButton1_Click()
    Dim rTarget As Range
    Dim i As Integer
    Dim TextFileImport As Variant
    TextFileImport = Application.GetOpenFilename("Text Files (*.txt),*.txt", , _
           "Select Text Data File", , True)
    For i = 1 To UBound(TextFileImport)
    Set rTarget = Worksheets("Center Office").Range("B65536").End(xlUp).Offset(1, 0)
    With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & TextFileImport(i), _
        Destination:=rTarget)
        .FieldNames = True
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With
    Next i
End Sub