Error importing sheets in VBA

1
votes

I'm trying to write a VBA macro to import a sheet from another workbook. I get a "subscript out of range" error on the "copy" line. The file opens correctly, but I'm not sure what goes wrong after that.

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim lastdate As String, filename As String

lastdate = Format(sheet_1.Range("D11") - 7, "ddmmyy")
filename = "C:\Dir\file " & lastdate & ".xlsm"

Workbooks.Open (filename)
Workbooks(filename).Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)

Workbooks(filename).Close

Application.ScreenUpdating = True
Application.DisplayAlerts = False

End Sub

Edit: error changed to "subscript out of range", code changed so worksheets referenced by index.

Code v2:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Dim lastdate As String, filename As String

lastdate = Format(sheet1.Range("D11") - 7, "ddmmyy")
filename = "C:\Dir\file " & lastdate & ".xlsm"

Dim wbk As Workbook
wbk = Workbooks.Open(filename)

wbk.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)
wbk.Close

End Sub
3
vbaexcel
where are sheet_3 and sheet_1 dimensioned and what datatype and values are they? – user2140173
This is me trying to prevent you from closing without saving: I think you want Application.DisplayAlerts = True at the end. Plus, you should always do error handling when setting that to False else you're headed for bad surprises fast. – Jean-François Corbett
Hi, I have edited above. I had used sheet_3 and sheet_1 as placeholders in simplifying down the code a little, and that was causing part of the error. However, there is still an underlying problem - when I do the same using indices I get a "subscript out of range" error (which I think was the original error). – ajor
Jean-Francois, you're right. Sorry, a typo! – ajor
@Jean-FrançoisCorbett Application.DisplayAlerts. according to Help, "If you set this property to False, Microsoft Excel sets this property to True when the code is finished, unless you are running cross-process code." – Mark Fitzgerald

3 Answers

3
votes

You get the subscript out of range error because you don't have the workbook's Name right. The Name isn't the same thing as the full path (which is what you have been assuming); it's the same thing as the filename.

This will work:

Workbooks.Open "C:\Dir\file1234.xlsm"
Workbooks("file1234.xlsm").Sheets(1).Range("a1").Value = "yay"

Whereas this won't:

Workbooks.Open "C:\Dir\file1234.xlsm"
Workbooks("C:\Dir\file1234.xlsm").Sheets(1).Range("a1").Value = "yay"

But a much better way to reference a workbook is to set an explicit reference to it like this:

Dim wbk As Workbook
Set wbk = Workbooks.Open(filename)
wbk.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)

With wbk you now have a handle on the workbook you want; you don't have to guess its name or anything.

1
votes

The keyword 'Set' is required when referencing an object variable:

Private Sub CommandButton1_Click()

Application.ScreenUpdating = False
'Application.DisplayAlerts = False '<<this does not look like it is required - when is an alert displayed in the method?

Dim lastdate As String, filename As String

lastdate = Format(sheet1.Range("D11") - 7, "ddmmyy")
filename = "C:\Dir\file " & lastdate & ".xlsm"

Dim wbk As Workbook
Set wbk = Workbooks.Open(filename)   '<<Set is required

wbk.Worksheets(2).Copy after:=ThisWorkbook.Worksheets(1)
wbk.Close

End Sub
-1
votes
Option Explicit

Sub Import_Worksheets()

Dim FolderPath As String
Dim Filename As String
Dim sheet As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

FolderPath = "H:G:\D S Class\Excel VBA (Macro)\RTO form\VBA\"
Filename = Dir(FolderPath & "RESTAURANT_USAGE_DATA.xlsx")

Do While Filename <> ""
 Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
 For Each sheet In ActiveWorkbook.Sheets
 sheet.Copy After:=ThisWorkbook.Sheets(1)
 Next sheet
 Workbooks(Filename).Close
 Filename = Dir()
Loop

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub

    enter code here