Excel Forum

Code: Select all

Sub ExpGPAVicha()
    Dim sFolderPath As String
    Dim wb1 As Workbook
    Dim ws1 As Worksheet
    Dim wb2 As Workbook
    Dim Path As String
    Dim FName As String
    
    On Error Resume Next
    Application.ScreenUpdating = False
    sFolderPath = "C:\" & Range("A20").Value

    If Dir(sFolderPath, vbDirectory) = "" Then
        MkDir sFolderPath
    End If
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.Sheets("ExPublish2")
    
    FName = ws1.Range("K2") & ".xlsx"
    ws1.Range("A:G").Copy
    Set wb2 = Workbooks.Add
    With wb2.ActiveSheet.Range("A:G")
        .PasteSpecial (xlValues)
        .PasteSpecial (xlFormats)
    Range("A2").Select
    Application.DisplayAlerts = True
    End With
    Application.DisplayAlerts = False
'    wb2.SaveAs FileFormat:="Excel Files (*.XLSX), *.XLSX"
    wb2.SaveAs Filename:=sFolderPath & "\" & FName
    Application.CutCopyMode = False
    wb2.Close
    Application.ScreenUpdating = True
'    Sheets("ScoreVicha").Select
MsgBox "ส่งออกไฟล์ไปไว้ที " & "C:\" & Range("A20").Value & "\" & Worksheets("ExPublish2").Range("K2").Value & ".xlsx" & " เรียบร้อยแล้ว"