Hello, since my pc was upgraded, this code will not run. It goes directly to the "File selection was canceled." It worked fine before the upgrade but I had to put PtrSafe declarations in the following to get it it even open. Any help would be greatly appreciated on ideas to fix???
Option Explicit
Private Declare PtrSafe Function ts_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (tsFN As tsFileName) As Boolean
Private Declare PtrSafe Function ts_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (tsFN As tsFileName) As Boolean
Private Declare PtrSafe Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Dim strCurrProjPath As String
Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strXlFileName As String 'Excel Workbook name
Dim strWorksheetName As String 'Excel Worksheet name
Dim objCell As Object 'Last used cell in column
Dim strUsedRange As String 'Used range
Dim FileName As String
Dim objDialog, boolResult
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
On Error GoTo Err_ImportSeveranceData
strFilter = "All Files (*.*)" & vbNullChar & "*.*"
lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly
varFileName = tsGetFileFromUser( _
fOpenFile:=True, _
strFilter:=strFilter, _
rlngflags:=lngFlags, _
strInitialDir:="C:\Windows", _
strDialogTitle:="Find File (Select The File And Click The Open Button)")
'remove the strInitialDir:="C:\Windows", _ line if you do not want the Browser to open at a specific location
If IsNull(varFileName) Or varFileName = "" Then
Debug.Print "User pressed 'Cancel'."
Beep
MsgBox "File selection was canceled.", vbInformation
Exit Sub
Else
tbFile = varFileName
End If
'Assign Path and filename of XL file to variable
strXlFileName = varFileName
'Assign Excel application to a variable
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False 'Can be visible or not visible
objExcel.UserControl = True
'Open the Excel Workbook
Set objWorkbook = objExcel.Workbooks.Open(strXlFileName)
', Password:="gap$term"
'Assign required worksheet to a variable
With objWorkbook
Set objWorksheet = .Worksheets(1)
End With
With objWorksheet
'Assign worksheet name to a string variable
strWorksheetName = .Name
End With
'Turn off/Close in reverse order to setting/opening.
Set objCell = Nothing
Set objWorksheet = Nothing
'SaveChanges = False suppresses save message
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
'Import the worksheet
DoCmd.TransferSpreadsheet acImport, 8, "tblSeveranceData", _
strXlFileName, True, "DynamicRange"
MsgBox "Severance data imported successfully!"
Option Explicit
Private Declare PtrSafe Function ts_apiGetOpenFileName Lib "comdlg32.dll" _
Alias "GetOpenFileNameA" (tsFN As tsFileName) As Boolean
Private Declare PtrSafe Function ts_apiGetSaveFileName Lib "comdlg32.dll" _
Alias "GetSaveFileNameA" (tsFN As tsFileName) As Boolean
Private Declare PtrSafe Function CommDlgExtendedError Lib "comdlg32.dll" () As Long
Dim strCurrProjPath As String
Dim objExcel As Object 'Excel.Application
Dim objWorkbook As Object 'Excel.Workbook
Dim objWorksheet As Object 'Worksheet
Dim strXlFileName As String 'Excel Workbook name
Dim strWorksheetName As String 'Excel Worksheet name
Dim objCell As Object 'Last used cell in column
Dim strUsedRange As String 'Used range
Dim FileName As String
Dim objDialog, boolResult
Dim strFilter As String
Dim lngFlags As Long
Dim varFileName As Variant
On Error GoTo Err_ImportSeveranceData
strFilter = "All Files (*.*)" & vbNullChar & "*.*"
lngFlags = tscFNPathMustExist Or tscFNFileMustExist Or tscFNHideReadOnly
varFileName = tsGetFileFromUser( _
fOpenFile:=True, _
strFilter:=strFilter, _
rlngflags:=lngFlags, _
strInitialDir:="C:\Windows", _
strDialogTitle:="Find File (Select The File And Click The Open Button)")
'remove the strInitialDir:="C:\Windows", _ line if you do not want the Browser to open at a specific location
If IsNull(varFileName) Or varFileName = "" Then
Debug.Print "User pressed 'Cancel'."
Beep
MsgBox "File selection was canceled.", vbInformation
Exit Sub
Else
tbFile = varFileName
End If
'Assign Path and filename of XL file to variable
strXlFileName = varFileName
'Assign Excel application to a variable
Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = False 'Can be visible or not visible
objExcel.UserControl = True
'Open the Excel Workbook
Set objWorkbook = objExcel.Workbooks.Open(strXlFileName)
', Password:="gap$term"
'Assign required worksheet to a variable
With objWorkbook
Set objWorksheet = .Worksheets(1)
End With
With objWorksheet
'Assign worksheet name to a string variable
strWorksheetName = .Name
End With
'Turn off/Close in reverse order to setting/opening.
Set objCell = Nothing
Set objWorksheet = Nothing
'SaveChanges = False suppresses save message
objWorkbook.Close SaveChanges:=False
Set objWorkbook = Nothing
objExcel.Quit
Set objExcel = Nothing
'Import the worksheet
DoCmd.TransferSpreadsheet acImport, 8, "tblSeveranceData", _
strXlFileName, True, "DynamicRange"
MsgBox "Severance data imported successfully!"
