CODEWITHSUNDEEP
vba
akash bondre
akash bondre

Reputation: 63

Suscript Out of Range error only after Dynamic opening of input sheets, VBA

Below code works without error if I keep the two input excel files "CFileName" and "DFileName" open manually. But when I try to open these two files dynamically by uncommenting the code lines in below code then it gives me an error "Subscript Out of Range". I even tried inserting the Delays but didn't work.

Sub Comparator()

Dim PreFileName As String

Dim CFileName As String

Dim DFileName As String

Dim FileName1 As String

Dim FileName2 As String

Dim XLApp, XLDoc, DestObject As Object

Dim sh As Worksheet, wb As Workbook

Set XLApp = CreateObject("excel.Application")

CFileName = Application.GetOpenFilename(Filefilter:="excel Files,*.xlsx", Title:="Select the File to be processed")

' Set XLDoc = XLApp.Workbooks.Open(CFileName)

FileName1 = Mid(CFileName, InStrRev(CFileName, Application.PathSeparator) + 1)

XLApp.Visible = True

DFileName = Application.GetOpenFilename(Filefilter:="excel Files,*.xlsx", Title:="Select the File to be processed")

' Set XLDoc = XLApp.Workbooks.Open(DFileName)

FileName2 = Mid(DFileName, InStrRev(CFileName, Application.PathSeparator) + 1)

XLApp.Visible = True

Windows(FileName1).Activate

Workbooks(FileName1).Worksheets("Copy").Copy After:=Workbooks(FileName2).Worksheets(3)

End Sub

Upvotes: 0

Views: 66

Answers (1)

Sorceri
Sorceri

Reputation: 8033

Tested and Working.

Sub Comparator()

Dim PreFileName As String

Dim CFileName As String

Dim DFileName As String

Dim FileName1 As String

Dim FileName2 As String

Dim XLApp, XLDoc, DestObject As Object

Dim sh As Worksheet, wb As Workbook

Set XLApp = CreateObject("excel.Application")

CFileName = Application.GetOpenFilename(Filefilter:="excel Files,*.xlsx", Title:="Select the File to be processed")

 Set XLDoc = XLApp.Workbooks.Open(CFileName)

FileName1 = Mid(CFileName, InStrRev(CFileName, Application.PathSeparator) + 1)

XLApp.Visible = True

DFileName = Application.GetOpenFilename(Filefilter:="excel Files,*.xlsx", Title:="Select the File to be processed")

 Set DestObject = XLApp.Workbooks.Open(DFileName)

FileName2 = Mid(DFileName, InStrRev(CFileName, Application.PathSeparator) + 1)


XLDoc.Activate

XLDoc.Worksheets("Copy").Copy After:=DestObject.Worksheets(3)

End Sub

Upvotes: 1

Related Questions