Access VBA open file run report however hidden

[June7]

1. Every variable must be explicitly declared or it defaults to variant type

2. Quite should be Quit. Did you run debug compile?



3. Your looping code doesn't make sense to me.

4. Can only get code to copy between workbooks in same instance of Excel to work, as in the following:

Sub CopySheet()
Dim xl As Excel.Application, xlw1 As Excel.Workbook, xlw2 As Excel.Workbook
Dim x As Integer
Set xl = CreateObject("Excel.Application")
Set xlw2 = xl.Workbooks.Add
For x = 1 To 100
    Set xlw1 = xl.Workbooks.Open("C:\path\filename.xlsx", , True)
    xlw1.Sheets(1).Copy After:=xlw2.Sheets(xlw2.Sheets.Count)
    xlw2.ActiveSheet.Name = "T" & x & Left(xlw1.Name, InStr(xlw1.Name, ".") - 1)
    xlw1.Close
Next
xl.Visible = True
End Sub

5. I can manually open and close Excel while code is running and VBA Excel object remains not visible until the line to set visible.

[johnseito]

I did what you did with your example, x1 as both the open file and the Added new file before turning it into (what I posted) x1, and x2 as separate instance.
With x1 as the same instance, the issue that the added file, along with the open file becomes visible is still an issue. This is especially when I open a file (the
file in the directory that is running ) in the directory.

The looping is just to loop until end of file (maybe I typed/ copied it wrong). Quite is a typo.

I tested your code and I see the issue occurred. While is running I open filename the added file along with the open file becomes visible.
And I get an error message too.
I don't know if the option explicit would help with it (I may try out).

[June7]

I don't have several hundred workbooks to test Do Loop code. My code uses the same workbook to copy from for 100 iterations. I even manually open that wb while code executes. Cannot replicate issue. Perhaps I don't understand process you are trying to accomplish.

I just noticed in your posted code the variable P is not set
How does it get value? I also don't see how the code is cycling through files.

[johnseito]

You could create one file with data, copy it and repeat.
It grows exponentially really quickly. Soon I will have hundreds.

Sorry P is really path.

[June7]

Could, but don't really want to.

Well, I ran code again. Did get that popup error. Killed code and tried again. Did not get error.

Okay, modified test. Ran code and opened some other totally unrelated Excel file. That does cause the Excel files to be visible. Tried again, files did not become visible.

Cannot replicate issue consistently so probably cannot help.

[johnseito]

Alright no problem - I wonder why issue is not consistent.