Solved VBA Export to excel ..runtime error 91 (1 Viewer)

mansied

Member
Local time
Today, 06:44
Joined
Oct 15, 2020
Messages
99
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
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 05:44
Joined
Feb 28, 2001
Messages
27,001
Error 91 is "Object Variable Not Set" - meaning that something is being treated as, or recognized as, an object, but the object has not been set up properly to enable its use. You showed us

Code:
With f
  For Each varFile In .SelectedItems

BUT... variable f is not passed in to your subroutine AND is not initialized inside it. The function of a WITH statement is to make the next line read like

Code:
For Each varFile In f.SelectedItems

but f is not defined within the subroutine. Therefore, your problem is external to the subroutine. It ALSO reveals a dangerous practice - depending on side-effects. You appear to be depending something outside of your routine to have done something with f, but the error message says that in fact, it didn't. That's as far as I can take you given that I can't see anything about the origin of f.
 

mansied

Member
Local time
Today, 06:44
Joined
Oct 15, 2020
Messages
99
Error 91 is "Object Variable Not Set" - meaning that something is being treated as, or recognized as, an object, but the object has not been set up properly to enable its use. You showed us

Code:
With f
  For Each varFile In .SelectedItems

BUT... variable f is not passed in to your subroutine AND is not initialized inside it. The function of a WITH statement is to make the next line read like

Code:
For Each varFile In f.SelectedItems

but f is not defined within the subroutine. Therefore, your problem is external to the subroutine. It ALSO reveals a dangerous practice - depending on side-effects. You appear to be depending something outside of your routine to have done something with f, but the error message says that in fact, it didn't. That's as far as I can take you given that I can't see anything about the origin of f.
so how can I solve this issue ,..I am not sure to get what to do
 

mansied

Member
Local time
Today, 06:44
Joined
Oct 15, 2020
Messages
99
it does highlight varFile
1611763975022.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:44
Joined
May 21, 2018
Messages
8,463
In your project, make sure at the top of every module you have
Option Explicit
Then open References and check that you have no broken references
Then run debug compile, and fix all your problems. that do not compile

Unless this is a class module it poorly written code with a bunch of global variables.



debug.png
 

mansied

Member
Local time
Today, 06:44
Joined
Oct 15, 2020
Messages
99
Thanks,I am a new in VBA .
How can i find the broken library ?
I added Option Explicit

Still have error on this line

1611767381455.png
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 06:44
Joined
May 21, 2018
Messages
8,463
Tools, References

debug.png


Most likely you will have a MISSING or BROKEN listed. You will have to scroll down for the new version with a similar name. You have to uncheck the broken missing one first.
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 10:44
Joined
Sep 12, 2006
Messages
15,614
.selecteditems ought to be the selecteditems in a (multi-select) combobox or listbox, I would think. So you shouldn't need a reference, but you will need to know which control holds the selecteditems.

you cant just say

Code:
for each varitem in . selecteditems

it has to be
Code:
for each varitem in somelist.selecteditems

and if the control is on a form, then the code will need to be in the same form, or at any rate will need to be able to "see" the control.
 

Gasman

Enthusiastic Amateur
Local time
Today, 10:44
Joined
Sep 21, 2011
Messages
14,050
As already mentioned by @The_Doc_Man

What is f ????, more importantly, where is it defined?
 

mansied

Member
Local time
Today, 06:44
Joined
Oct 15, 2020
Messages
99
.selecteditems ought to be the selecteditems in a (multi-select) combobox or listbox, I would think. So you shouldn't need a reference, but you will need to know which control holds the selecteditems.

you cant just say

Code:
for each varitem in . selecteditems

it has to be
Code:
for each varitem in somelist.selecteditems

and if the control is on a form, then the code will need to be in the same form, or at any rate will need to be able to "see" the control.
It didn't accept any changes ... a bunch of errors comes up...

I think the issue is with library references and Access 365 new update. I don't know how to solve it..!!!!! i added somee references but .....
 

Users who are viewing this thread

Top Bottom