Hello
I need your help ,I had the below code for Export 3tables in Xl format in a folder .it was working perfectly ,but after updating Access 365 I have runtime error 91.. in the line " For Each varFile In .SelectedItems "... can you help me to solve it .thanks alot
Public Function export()
DoCmd.SetWarnings False
With f
For Each varFile In .SelectedItems
Debug.Print Dir(varFile)
Dim Name As String
Name = Dir(varFile)
'InString_350 = InStr(1, Str(varFile), "CL350", vbTextCompare)
'Debug.Print InStr(1, Dir(varFile), "CL350")
Dim excelApp As New Excel.Application
Dim xlwkb As Workbook
Dim xlsht As Worksheet
Dim FileName_2 As String
FileName_2 = Application.CurrentProject.Path & "\NewExcel\" & Name
On Error GoTo Error_
Set xlwkb = excelApp.Workbooks.Open(FileName_2)
Set xlsht = xlwkb.Worksheets(2)
xlsht.UsedRange.Delete
xlwkb.Save
xlwkb.Close
Debug.Print FileName_2
InString_350 = InStr(1, Name, "CL350")
InString_650 = InStr(1, Name, "CL650")
Debug.Print InString_350
Debug.Print InString_650
If (InString_350 > 0 And InString_650 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_350", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
Dim i As Integer
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
ElseIf (InString_650 > 0 And InString_350 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_650", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
ElseIf (InString_350 = 0 And InString_650 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_Global", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
End If
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DATA2", FileName_2, True
Next
End With
Exit Function
Error_:
If (Err.Number = 50290) Then Resume
Resume
End Function
I need your help ,I had the below code for Export 3tables in Xl format in a folder .it was working perfectly ,but after updating Access 365 I have runtime error 91.. in the line " For Each varFile In .SelectedItems "... can you help me to solve it .thanks alot
Public Function export()
DoCmd.SetWarnings False
With f
For Each varFile In .SelectedItems
Debug.Print Dir(varFile)
Dim Name As String
Name = Dir(varFile)
'InString_350 = InStr(1, Str(varFile), "CL350", vbTextCompare)
'Debug.Print InStr(1, Dir(varFile), "CL350")
Dim excelApp As New Excel.Application
Dim xlwkb As Workbook
Dim xlsht As Worksheet
Dim FileName_2 As String
FileName_2 = Application.CurrentProject.Path & "\NewExcel\" & Name
On Error GoTo Error_
Set xlwkb = excelApp.Workbooks.Open(FileName_2)
Set xlsht = xlwkb.Worksheets(2)
xlsht.UsedRange.Delete
xlwkb.Save
xlwkb.Close
Debug.Print FileName_2
InString_350 = InStr(1, Name, "CL350")
InString_650 = InStr(1, Name, "CL650")
Debug.Print InString_350
Debug.Print InString_650
If (InString_350 > 0 And InString_650 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_350", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
Dim i As Integer
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
ElseIf (InString_650 > 0 And InString_350 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_650", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
ElseIf (InString_350 = 0 And InString_650 = 0) Then
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12, "EXPORT_Global", FileName_2, Yes, "Report Details!"
Workbooks.Open(FileName_2).Sheets("Report Details").Activate
i = Cells(Rows.Count, 1).End(xlUp).Row - 1
ActiveWorkbook.Close False
DoCmd.RunSQL ("INSERT INTO ExportedRows (Rows) VALUES ('" & i & "');")
End If
'DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "DATA2", FileName_2, True
Next
End With
Exit Function
Error_:
If (Err.Number = 50290) Then Resume
Resume
End Function