CODEWITHSUNDEEP
vbaexcel
walkens
walkens

Reputation: 59

Error when opening workbook by using VBA

I've tried several ways to open a separate workbook in one workbook, but always get error message Application-defined or object-defined error, the following is my code:

Sub PrepaidImport()
Dim x As Workbook, y As Workbook, vals As Variant, MyFile1 As String, MyFile2 As String
MyFile1 = Application.GetOpenFilename()
Set x = Workbooks.Open(MyFile1)
End Sub

the following code get the same error as well:

Sub PrepaidImport()
Dim x As Workbook, y As Workbook, vals As Variant, MyFile1 As String, MyFile2 As String
Set x = Workbooks.Open("M:\Company\2014\YTD 2014 Prepaid Assets.xlsx")
End Sub

The following code get a diff error because I'm trying to open a binary workbook:

Sub PrepaidImport()
Dim y as Workbook
Set y = Workbooks.Open("Y:\Branch\Prepaid Assets Amortization Import Template.xlsb")
End Sub

This is my entire code:

Sub PrepaidImport()
Dim x As Workbook, y As Workbook, vals As Variant, MyFile1 As String, MyFile2 As String

Set x = Workbooks.Open("M:\Company\2014\YTD 2014 Prepaid Assets.xlsx")
Set y = Workbooks.Open("Y:\Branch\Prepaid Assets Amortization Import Template.xlsb")

vals = x.Worksheets("11.2014").Range("A6", "A" &x.Worksheets("11.2014").Range("A6").End(xlDown)).Value
Set y.Worksheets("Journal_Details").Range("Y1").Value = vals

x.Close

End Sub

The files are opened, but error message pops out and makes me unable to run the subsequent code. I'm on Excel 2007.

Please help!

Upvotes: 1

Views: 1122

Answers (1)

Mark Moore
Mark Moore

Reputation: 510

The problem is in your second to last line, you shouldn't have the "set" statement in front of the assignment line, i.e. it should just be

y.Worksheets("Journal_Details").Range("Y1").Value = vals

Upvotes: 0

Related Questions