Need help - VBA to open folder

[sparkytech]

I have the following code (created by others, modded by me) that I am trying to further modify with no luck so far:

Sub ExportCleanExcel(control As IRibbonControl)

'Exports the information in the master sheet to a chosen excel file; will just move the data without macros or other extra features
Dim FileToOpen As Variant
Dim DestWkb As Workbook
Dim MasBotRow As Long
Dim MasLasCol As Long

'Turn off screen updates to improve performance
Application.ScreenUpdating = False

'Allows user to select a file to export to using the traditional open file window
FileToOpen = Application.GetOpenFilename("All Excel Files (*.xls?), *.xls?", , "Please export file")

'If the user selects cancel when choosing a file to open FileToOpen is set to FALSE
'Using this we can check if a file was actually selected before continuing
If FileToOpen <> False Then

'Find the bottom row and last column of the table on the master sheet
MasBotRow = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row

MasLasCol = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, SearchOrder:=xlByColumns, SearchDirection:=xlPrevious, MatchCase:=False).Column

'Open the file that was selected and then set that workbook as DestWkb
Set DestWkb = Application.Workbooks.Open(FileToOpen)

'Copies rows A5 to W to last row and also Z5 to last row on sheet {skips "X:Y"}

ThisWorkbook.Sheets("Master").Range("A5:W" & MasBotRow).Copy
DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues

ThisWorkbook.Sheets("Master").Range("Z5:Z" & MasBotRow).Copy
DestWkb.Sheets(1).Range("X5:X" & MasBotRow).PasteSpecial xlPasteValues

'Turn off alerts to avoid clipboard notices when closing the file
Application.DisplayAlerts = False

'Close the data file
DestWkb.Close True

'Turn back on alerts after closing the file
Application.DisplayAlerts = True

End If

'Turn back on screen updates
Application.ScreenUpdating = True

MsgBox "Export Complete"

End Sub

This code opens a popup to allow the user to browse to C:\2022, select a file, and then it exports the active sheet to another workbook. I want to modify this to open "C:\2022" every time, and then the end user can select a file. I would also like to change this to only show "Export Complete" if the macro runs completely. The way it is now, the user can click cancel, and it still shows "Export Complete". I am going to continue working away at this, but any help would be greatly appreciated!

 

[dmt32]

Hi,
untested but see if this update to your code does what you want

Sub ExportCleanExcel(control As IRibbonControl)
    
    'Exports the information in the master sheet to a chosen excel file;
    'will just move the data without macros or other extra features
    Dim FileToOpen  As Variant
    Dim DestWkb     As Workbook
    Dim MasBotRow   As Long
    Dim CurrentDir  As String
    
    'Turn off screen updates to improve performance
     With Application
        .ScreenUpdating = False: .DisplayAlerts = False
     End With
    
    CurrentDir = CurDir
    ChDir = "C:\2022"
    
    'Allows user to select a file to export to using the traditional open file window
    FileToOpen = Application.GetOpenFilename("All Excel Files (*.xls?), *.xls?", , "Please export file")
    
    'If the user selects cancel when choosing a file to open FileToOpen is set to FALSE
    'Using this we can check if a file was actually selected before continuing
    If FileToOpen <> False Then
        
        'Find the bottom row and last column of the table on the master sheet
        MasBotRow = Sheet1.Cells.Find(What:="*", After:=Range("A1"), LookAt:=xlPart, LookIn:=xlFormulas, _
                                     SearchOrder:=xlByRows, SearchDirection:=xlPrevious, MatchCase:=False).Row
           
        'Open the file that was selected and then set that workbook as DestWkb
        Set DestWkb = Application.Workbooks.Open(FileToOpen)
        
        'Copies rows A5 to W to last row and also Z5 to last row on sheet {skips "X:Y"}
        
        ThisWorkbook.Sheets("Master").Range("A5:W" & MasBotRow).Copy
        DestWkb.Sheets(1).Range("A5:W" & MasBotRow).PasteSpecial xlPasteValues
        
        ThisWorkbook.Sheets("Master").Range("Z5:Z" & MasBotRow).Copy
        DestWkb.Sheets(1).Range("X5:X" & MasBotRow).PasteSpecial xlPasteValues
        
        'Close the data file
        DestWkb.Close True
        
        MsgBox "Export Complete", 64, "Complete"
        
    End If

    ChDir = CurrentDir
    'Turn back on screen updates
    'Turn back on alerts after closing the file
    With Application
        .ScreenUpdating = True: .DisplayAlerts = True: .CutCopyMode = False
    End With
    
End Sub

Dave

 

[sparkytech]

Thanks for the quick reply. When I run the code, I get Compile Error: Argument not optional on the "ChDir = "C:\2022" Not sure what that means yet...

 

[dmt32]

Thanks for the quick reply. When I run the code, I get Compile Error: Argument not optional on the "ChDir = "C:\2022" Not sure what that means yet...

sorry my error it should be

ChDir "C:\2022"

Dave

 

[sparkytech]

sorry my error it should be

ChDir "C:\2022"

Dave

Thanks, its working now. Thanks for saving me a ton of grief... couldn't figure this out.

 

[dmt32]

Thanks, its working now. Thanks for saving me a ton of grief... couldn't figure this out.

welcome - glad resolved

Dave