The string is if you wanted to set a saved file name and pathway by default. This is instead of asking them to save a filename. I just realised you need the filepathcode as well
You need to have '“Do not allow loading or saving of other workbooks” unticked.
Also as a tip for debugging when I generate an exe allow “Show Developer Tab” . This is not the final version I release to customers but it enables you to open the exe file, open the VBA editor and then select the troublesome procedure and either run using F8 or just run as normal but you can view what line is generating the error.
I haven’t used the xlWBATWorksheet before.
Workbooks.Add is simpler and works just fine
Try this code. (It was adapted from my code. Some dims might not be used.
Private Sub EXPORT()
'Based on Upgrade 3
’ Macro to import data during upgrade
'Dim DestinationFile As String
Dim SourceFile As String
Dim Filepath As String
Dim strFile As String
Dim DestinationFilepath As String
Dim count As Integer
Dim wb As Workbook
Dim DestinationWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim intChoice As Integer
Dim Ret
'No need to set as set as public
'Dim DestinationWorkbook As Workbook
'Dim SourceWorkbook As Workbook
Application.DisplayAlerts = True
'Check Security Access
'Set file path
Set SourceWorkbook = Application.ActiveWorkbook
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
Filepath = XLSPadlock.PLEvalVar("EXEPath")
'Test if Directory exists
If Dir(Filepath & "Export\", vbDirectory) = "" Then
MkDir Filepath & "Export\"
End If
'Set file name of destination workbook
strFile = " Export Data " & " Exp " _
& Format(Now(), “dd mm yy hh mm”)
strFile = Filepath & “Export” & strFile
Workbooks.Add
Set DestinationWorkbook = ActiveWorkbook
Set XLSPadlock = Application.COMAddIns(“GXLS.GXLSPLock”).Object
XLSPadlock.SetOption Option:=“2”, value:=“0”
XLSPadlock.SetOption Option:=“1”, value:=“1”
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFile, ReadOnlyRecommended:=False, CreateBackup:=False
Application.DisplayAlerts = True
XLSPadlock.SetOption Option:=“2”, value:=“1” 'resets default save behaviour
XLSPadlock.SetOption Option:=“1”, value:=“0”
'Create worksheet tabs
Worksheets.Add
ActiveSheet.Name = "SheetA"
Worksheets.Add
ActiveSheet.Name = "SheetB"
Worksheets.Add
ActiveSheet.Name = "SheetC"
SourceWorkbook.Activate
Application.ScreenUpdating = True
Application.Calculation = xlCalculationManual
'Application.ScreenUpdating = False
Application.DisplayStatusBar = False
Application.EnableEvents = False
ActiveSheet.DisplayPageBreaks = False
'Application.DisplayAlerts = False
DestinationWorkbook.Worksheets("SheetA").Range("A1:C3").value = _
SourceWorkbook.Worksheets("SheetA").Range("A1:C3").value
DestinationWorkbook.Worksheets("SheetB").Range("B3").value = _
SourceWorkbook.Worksheets("SheetB").Range("B3").value
DestinationWorkbook.Worksheets("SheetC").Range("B1:C3").value = _
SourceWorkbook.Worksheets("SheetC").Range("B1:C3").value
Set XLSPadlock = Application.COMAddIns("GXLS.GXLSPLock").Object
XLSPadlock.SetOption Option:="2", value:="0"
XLSPadlock.SetOption Option:="1", value:="1"
DestinationWorkbook.Close savechanges:=True
XLSPadlock.SetOption Option:="2", value:="1" 'resets default save behaviour
XLSPadlock.SetOption Option:="1", value:="0"
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
MsgBox “Export file completed.”
End Sub
Sub TransferData()
Dim SourceFile As String
Dim Filepath As String
Dim strFile As String
Dim DestinationFilepath As String
Dim count As Integer
Dim wb As Workbook
Dim DestinationWorkbook As Workbook
Dim SourceWorkbook As Workbook
Dim intChoice As Integer
Dim Ret
'Test if file is new
Dim myFile As String
myFile = GetSecureWorkbookFilename
'Set file path
Set DestinationWorkbook = Application.ActiveWorkbook
Dim XLSPadlock As Object
Set XLSPadlock = Application.COMAddIns("GXLSForm.GXLSFormula").Object
Filepath = XLSPadlock.PLEvalVar("EXEPath")
'Filepath = Application.ActiveWorkbook.Path
Filepath = Filepath & "Export\"
'only allow the user to select one file
'only allow the user to select one file
With Application.FileDialog(msoFileDialogOpen)
.AllowMultiSelect = False
.Title = “Select File to be Upgraded. You will need to select the data file as well.”
.InitialFileName = Filepath
.Filters.Clear
.Filters.Add “Back Up Data file”, “*.xlsx”
End With
'make the file dialog visible to the user
intChoice = Application.FileDialog(msoFileDialogOpen).Show
'determine what choice the user made
If intChoice <> 0 Then
'get the file path selected by the user
SourceFile = Application.FileDialog( _
msoFileDialogOpen).SelectedItems(1)
'MsgBox "File selected"
Else
MsgBox "No File Selected - Import cancelled"
Application.Run "Go_To_Main_Menu"
Exit Sub
End If
Workbooks.Open SourceFile
'DestinationFilepath = Application.ActiveWorkbook.Path
Set SourceWorkbook = ActiveWorkbook
DestinationWorkbook.Worksheets("SheetA").Range("A1:C3").value = _
SourceWorkbook.Worksheets("SheetA").Range("A1:C3").value
DestinationWorkbook.Worksheets("SheetB").Range("B3").value = _
SourceWorkbook.Worksheets("SheetB").Range("B3").value
DestinationWorkbook.Worksheets("SheetC").Range("B1:C3").value = _
SourceWorkbook.Worksheets("SheetC").Range("B1:C3").value
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
Application.DisplayStatusBar = True
Application.EnableEvents = True
'ActiveSheet.DisplayPageBreaks = True
Application.EnableEvents = True
End Sub