Sheets("Sheet1").Move After:=Sheets("Sheet2") doesn't work twice

[surykatka]

I try to move sheets in 2 Excel files. Will anyone help me and answer, why the code below doesn't work? The first moving is done, but doing second VBA generates error: "Run-time error '1004': Method 'Sheets' of object '_Global' failed".
Platform: MS Access 2010, Excel 2010, Windows 7. References on: Visual Basic for Applications, Microsoft Access 14.0 Object Library. Microsoft Excel 14.0 Object Library, OLE Automation, Microsoft ActiveX Data Objects 6.0 Library.

Sub test()
Dim my_file As Object
Set my_file = CreateObject("Excel.Application")

my_file.Visible = True
my_file.UserControl = True
my_file.Workbooks.Open ("c:\data\test_1_.xlsx")

my_file.Sheets("Sheet1").Move After:=Sheets("Sheet2")

my_file.ActiveWorkbook.Save
my_file.Quit
Set my_file = Nothing
Set my_file = CreateObject("Excel.Application")

my_file.Visible = True
my_file.UserControl = True
my_file.Workbooks.Open ("c:\data\test_2_.xlsx")

my_file.Sheets("Sheet1").Move After:=Sheets("Sheet2") 'error occurs on this line

my_file.ActiveWorkbook.Save
my_file.Quit
Set my_file = Nothing
End Sub

 

[xenou]

I don't like this line, because you aren't exercising control over *what workbook* the sheets are in:

my_file.Sheets("Sheet1").Move After:=Sheets("Sheet2")

Instead, create workbook objects and reference them clearly:

Sub test()
Dim XLApp As Object
Dim wb As Object

    '//Create Excel Application Object
    Set XLApp = CreateObject("Excel.Application")

    '//Open workbook and move sheet
    Set wb = XLApp.Workbooks.Open ("c:\data\test_1_.xlsx")
    wb.Sheets("Sheet1").Move After:=Sheets("Sheet2")
    wb.Save
    wb.Close
    
    '//Open workbook and move sheet
    Set wb = XLApp.Workbooks.Open ("c:\data\test_2_.xlsx")
    wb.Sheets("Sheet1").Move After:=Sheets("Sheet2")
    wb.Save
    wb.Close
    
    '//Quit Excel
    XLApp.Quit
    Set XLApp = Nothing

    End Sub

I don't the point of user control being true here - the user doesn't need control (workbook visible can be nice while testing but you don't need that either).

 

[PDXActuary]

Good evening, I am having a similar problem but not able to extend your solution for surykatka to my code. I am running Excel 2010 and the objective of this macro is to go through a folder containing about 2,000 Excel documents (varying extensions) and extract the same data from each one and copy it onto the worksheet 'Values' in DataImportTool.xlsm. The "CopyOut" step is necessary because some calculations are performed on the imported data before it is transfered to the 'Values' worksheet. The macro ran swimmingly through the first 1,600 workbooks then stopped with the error "Run-time error '1004': Method 'Open' of object 'Workbooks' failed. I have not been able to run it successfully since. I continue to get the same error. Thank you in advance.



Sub GetQuoteInfo()
Dim FolderPath As String
Dim FileName As String
Dim WorkBk As Workbook
Dim Dest As Workbook
Dim nRow As Long
Dim WorkSt As Worksheet
Dim flg As Boolean

Set Dest = Workbooks("DataImportTool.xlsm")
nRow = 1885

'Point to the location of the files
FolderPath = "L:\UW_PROJ\Med_Man\Remaining\"

'Call Dir the first time; point to all Excel files in the folder path
FileName = Dir("L:\UW_PROJ\Med_Man\Remaining\" & "*.xl*")

'Loop until Dir returns an empty string
Do While FileName <> ""

flg = False

'Open a workbook in the folder
Set WorkBk = Workbooks.Open(FolderPath & FileName, UpdateLinks:=False)

'Check to see if there is a worksheet called 'Input'
'If there is not, close and proceed to the next workbook

'If WorksheetExists("Input") Then

For Each WorkSt In Worksheets
If WorkSt.Name Like "*Input*" Then flg = True: Exit For
Next

If flg = True Then

'Copy and paste the census
WorkBk.Worksheets("Input").Range("U6:X1005").Copy
Dest.Worksheets("Demo").Range("A5").PasteSpecial xlPasteValues

'Copy and paste the quote information
WorkBk.Worksheets("Quote51").Cells.Copy
Dest.Worksheets("CopyIn").Cells.PasteSpecial xlPasteValues

'At this point, the data on "CopyOut" will be populated
'Copy values over to "Values" in row nRow
Dest.Worksheets("CopyOut").Range("A3:BA3").Copy
Dest.Worksheets("Values").Range("A" & nRow).PasteSpecial _
xlPasteValues

WorkBk.Close savechanges:=False
nRow = nRow + 1

Else: WorkBk.Close savechanges:=False

End If

FileName = Dir()
Loop

End Sub

 

[surykatka]

Thanks xenou for reply. Your solution is O.K., but I must set the XLApp to "nothing" in the middle of code. Then problem is still on.

Sub test2()
Dim XLApp As Object
Dim wb As Object
Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
XLApp.UserControl = True
Set wb = XLApp.Workbooks.Open("c:\data\test_1_.xlsx")
wb.Sheets("Sheet1").Move After:=Sheets("Sheet2")
wb.Save
wb.Close
XLApp.Quit
Set XLApp = Nothing

Set XLApp = CreateObject("Excel.Application")
XLApp.Visible = True
XLApp.UserControl = True
Set wb = XLApp.Workbooks.Open("c:\data\test_2_.xlsx")
wb.Sheets("Sheet1").Move After:=Sheets("Sheet2") 'on this line occurs VBA error "Run-time error '1004': Method 'Sheets' of object '_Global' failed"
wb.Save
wb.Close
XLApp.Quit
Set XLApp = Nothing
End Sub

 

[xenou]

I wasn't taking my own advice: use the wb reference after you create it.

Sub test()
Dim XLApp As Object
Dim wb As Object

    '//Create Excel Application Object
    Set XLApp = CreateObject("Excel.Application")

    '//Open workbook and move sheet
    Set wb = XLApp.Workbooks.Open("c:\data\test_1_.xlsx")
    wb.Sheets("Sheet1").Move After:=[COLOR="#FF0000"]wb.[/COLOR]Sheets("Sheet2")
    wb.Save
    wb.Close
    
    '//Open workbook and move sheet
    Set wb = XLApp.Workbooks.Open("c:\data\test_2_.xlsx")
    wb.Sheets("Sheet1").Move After:=[COLOR="#FF0000"]wb.[/COLOR]Sheets("Sheet2")
    wb.Save
    wb.Close
    
    '//Quit Excel
    XLApp.Quit
    Set XLApp = Nothing

    End Sub

Or with With-End blocks (better):

[COLOR="Navy"]Sub[/COLOR] test()
[COLOR="Navy"]Dim[/COLOR] XLApp [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]
[COLOR="Navy"]Dim[/COLOR] wb [COLOR="Navy"]As[/COLOR] [COLOR="Navy"]Object[/COLOR]

    [COLOR="SeaGreen"]'//Create Excel Application Object[/COLOR]
    [COLOR="Navy"]Set[/COLOR] XLApp = CreateObject("Excel.Application")

    [COLOR="SeaGreen"]'//Open workbook and move sheet[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = XLApp.Workbooks.Open("c:\data\test_1_.xlsx")
    [COLOR="Navy"]With[/COLOR] wb
        .Sheets("Sheet1").Move After:=.Sheets("Sheet2")
        .Save
        .Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="SeaGreen"]'//Open workbook and move sheet[/COLOR]
    [COLOR="Navy"]Set[/COLOR] wb = XLApp.Workbooks.Open("c:\data\test_2_.xlsx")
    [COLOR="Navy"]With[/COLOR] wb
        .Sheets("Sheet1").Move After:=.Sheets("Sheet2")
        .Save
        .Close
    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]With[/COLOR]
    
    [COLOR="SeaGreen"]'//Quit Excel[/COLOR]
    Set wb = Nothing
    XLApp.Quit
    [COLOR="Navy"]Set[/COLOR] XLApp = [COLOR="Navy"]Nothing[/COLOR]

    [COLOR="Navy"]End[/COLOR] [COLOR="Navy"]Sub[/COLOR]

I don't understand your claim that you must quit the xlapp in the middle. You can open a workbook, close it, open another, and close it - all with the same app. In any case, you can still get it working if you use the reference variables to best effect as shown. More detail on using excel objects correctly in access is given here: http://blogs.office.com/b/microsoft...-objects-when-referring-to-excel-in-code.aspx

 

[surykatka]

The point of the problem is that the first run of "move after" is O.K., but the second ends with error. If you run this:

Sub test()
Dim XLApp As Object
Dim wb As Object
'//Create Excel Application Object
Set XLApp = CreateObject("Excel.Application")
'//Open workbook and move sheet
Set wb = XLApp.Workbooks.Open("c:\data\test_1_.xlsx")
With wb
.Sheets("Sheet1").Move After:=.Sheets("Sheet2")
.Save
.Close
End With
'//Quit Excel
Set wb = Nothing
XLApp.Quit
Set XLApp = Nothing
End Sub

once it is O.K. But the second run of the same instruction after a moment fails. Of course even if you reconfigure the order of sheets manually (outside of VBA). The same efect is when I quit the XLApp in the middle. I want to understand, why the first run is O.K. and the second run ends with error.

 

[xenou]

I don't know why it worked in your original code once but not twice. But since that code is not written properly it's irrelevant (to me, anyway). What code are you using now? The last code you posted doesn't even try to open the second workbook at all

ξ

 

[surykatka]

Here is exact my code:

Sub test3()
Dim nasz_plik As Object
Set nasz_plik = CreateObject("Excel.Application")

nasz_plik.Visible = True
nasz_plik.UserControl = True
nasz_plik.Workbooks.Open ("c:\data\test_1_.xlsx")

nasz_plik.Sheets("Sheet1").Move After:=Sheets("Sheet2")

nasz_plik.ActiveWorkbook.Save
nasz_plik.Quit
Set nasz_plik = Nothing
End Sub

Running second time it stops on line:

nasz_plik.Sheets("Sheet1").Move After:=Sheets("Sheet2")

 

[RoryA]

Still not properly qualified:

Sub test3()
Dim nasz_plik As Object
Set nasz_plik = CreateObject("Excel.Application")

nasz_plik.Visible = True
nasz_plik.UserControl = True
nasz_plik.Workbooks.Open ("c:\data\test_1_.xlsx")

nasz_plik.Sheets("Sheet1").Move After:=nasz_plik.Sheets("Sheet2")

nasz_plik.ActiveWorkbook.Save
nasz_plik.Quit
Set nasz_plik = Nothing
End Sub

 

[surykatka]

Oh, that was the point!!!
Thank you very very very much