File Not Found dialog box

[willyboy58]

'In the Prop1Invoice folder I have the Prop1Invoice workbook that has three invoice sheets that are copied to the Propxfer workbook. But right before they are copied, a "File Not Found" dialog box appears with the Prop1Invoice folder and the "propxfer" filename. When I select Cancel, another File Not Found dialog box appears with the Prop1Invoice folder, but this time with the "propxfer.xls" filename (note the .xls).

When I select Cancel this time, my code continues to it proper completion. My code has both the Application.ScreenUpdating and DisplayAlerts set to False and then True at the end.

'By using the Stop statement I have _
'narrowed my problem down to the following bit of code.

Stop
Workbooks.Open Filename:="C:\MSOffice\Prop1Invoice\Prop1Invoice.xls", _
UpdateLinks:=3 'this opens Prop1Invoice so its Invoice _
sheets can be copied to the Propxfer workbook. _

'I have verified that the Propxfer workbook is already _
open at this time.
Workbooks("prop1invoice&quot.Worksheets("InvoicePage3&quot.Copy Before:=Workbooks("propxfer.xls&quot.Sheets(1)
Workbooks("prop1invoice&quot.Worksheets("InvoicePage2&quot.Copy Before:=Workbooks("propxfer.xls&quot.Sheets(1)
Workbooks("prop1invoice&quot.Worksheets("InvoicePage1&quot.Copy Before:=Workbooks("propxfer.xls&quot.Sheets(1)
Stop

'Why is the File Not Found dialog box opening if the Prop1Invoice and Propxfer workbooks are already opened? Why is it looking for the "propxfer" file in the "Prop1Invoice" folder?

TIA

Bill

 

[BuGlen]

Hi Bill,
It looks like what's happening is you've left of the .xls in the prop1invoice workbook reference. I would also suggest setting up a workbook object in this scenerio instead of trying to manipulate the workbooks collection directly as it may save you some headaches in the long run. The following code shows how to set up the workbook object and copy sheets from an external file into the workbook from which the code is run:

-----

Sub Import()

    Dim wkbImport As Workbook
    
    Set wkbImport = Workbooks.Open(Filename:="C:\MyPath\MyWorkbook.xls", _
                        UpdateLinks:=True)
                        
    With wkbImport
        .Worksheets("ImportSheetName1").Copy Before:=ThisWorkbook.Sheets(1)
        .Worksheets("ImportSheetName2").Copy Before:=ThisWorkbook.Sheets(1)
        .Worksheets("ImportSheetName3").Copy Before:=ThisWorkbook.Sheets(1)
        .Close
    End With
    
    Set wkbImport = Nothing

End Sub

-----
Hope this helps.


Glen Appleton

VB.Net student.

 

[willyboy58]

BuGlen,

Same result with the File Not Found box. Still have "propxfer" the first time and "propxfer.xls" the second. After Canceling both, the code runs as expected. I had to adjust your code just a bit:

Sub OpenAndCopyInvoices2()

Dim wkbImport As Workbook

Set wkbImport = Workbooks.Open _(Filename:="C:\MSoffice\Prop1Invoice\Prop1Invoice.xls", _
UpdateLinks:=True)

With wkbImport
.Worksheets("InvoicePage1&quot.Copy Before:=Workbooks("propxfer&quot.Sheets(1)
.Worksheets("InvoicePage2&quot.Copy Before:=Workbooks("propxfer&quot.Sheets(1)
.Worksheets("InvoicePage3&quot.Copy Before:=Workbooks("propxfer&quot.Sheets(1)
'.Close REM'd b/c I need Prop1Invoice to stay open for later use
End With

Set wkbImport = Nothing

End Sub

Thoughts?

Bill

 

[BuGlen]

Hi Bill,

If you're running the code from the propxfer.xls workbook, then you should be using ThisWorkbook as a reference to the "self" workbook object, not the named instance Workbooks("propxfer&quot. It probably looked like I was using that reference like the "MyPath" reference as an example, but ThisWorkbook is actually a VB object reference to the current workbook. Try this:

-----

Sub OpenAndCopyInvoices2()

    Dim wkbImport As Workbook
    
    Set wkbImport = Workbooks.Open _(Filename:="C:\MSoffice\Prop1Invoice\Prop1Invoice.xls", _
                        UpdateLinks:=True)
                        
    With wkbImport
        .Worksheets("InvoicePage1").Copy Before:=ThisWorkbook.Sheets(1)
        .Worksheets("InvoicePage2").Copy Before:=ThisWorkbook.Sheets(1)
        .Worksheets("InvoicePage3").Copy Before:=ThisWorkbook.Sheets(1)
        '.Close  REM'd b/c I need Prop1Invoice to stay open for later use
    End With
    
    Set wkbImport = Nothing

End Sub

-----

If you're running the code from an entirely different workbook, then create an object reference to the propxfer.xls like this:

-----

Sub OpenAndCopyInvoices2()

    Dim wkbImport As Workbook
    Dim wkbXFer As Workbook
    
    Set wkbImport = Workbooks.Open _
        (Filename:="C:\MSoffice\Prop1Invoice\Prop1Invoice.xls", _
                        UpdateLinks:=True)
    Set wkbXFer = Workbooks.Open _
        (Filename:="C:\MyPath\propxfer.xls", UpdateLinks:=True)
                        
    With wkbImport
        .Worksheets("InvoicePage1").Copy Before:=wkbXFer.Sheets(1)
        .Worksheets("InvoicePage2").Copy Before:=wkbXFer.Sheets(1)
        .Worksheets("InvoicePage3").Copy Before:=wkbXFer.Sheets(1)
        '.Close  REM'd b/c I need Prop1Invoice to stay open for later use
    End With
    
    Set wkbImport = Nothing
    Set wkbXFer = Nothing

End Sub

-----

Hope this helps.


Glen Appleton

VB.Net student.