Jeffrey Mahoney
Well-known Member
- Joined
- May 31, 2015
- Messages
- 3,142
- Office Version
- 365
- Platform
- Windows
How do I reference a file opened in Excel that is not an Excel workbook?
I'm helping a client by creating an import tool and a pivot table creation. She generates an exported file from the website and it automatically opens in Excel. I normally access the file by setting the workbook and worksheet as objects like in the code below. As you can see, I commented out the first part of the code because it didn't work, nor did the second method.
I'm helping a client by creating an import tool and a pivot table creation. She generates an exported file from the website and it automatically opens in Excel. I normally access the file by setting the workbook and worksheet as objects like in the code below. As you can see, I commented out the first part of the code because it didn't work, nor did the second method.
Code:
Sub ImportTotalAllocation()
Dim TWB As Workbook
Dim iWB As Workbook
Dim RawSht As Worksheet
Dim iSht As Worksheet
Dim Cel As Range
Dim R As Range
Dim Sht As Worksheet
Dim WB As Workbook
Dim found As Boolean
Dim iRng As Range
Dim OutR As Range
Set TWB = ThisWorkbook
Set RawSht = TWB.Sheets("RAW")
On Error GoTo NoTotLabAllocation
Windows("ReportsMaster.aspx").Activate
On Error GoTo 0
Set iWB = ActiveWorkbook
' For Each WB In Application.Workbooks
' If WB.Name = "ReportsMaster.aspx" Then
' Set iWB = WB
' Exit For
' End If
' Next WB
' If found = False Then
' MsgBox "The ReportsMaster.aspx workbook isn't open, Please export the Total Allocation Report and try again"
' Exit Sub
' End If
For Each Sht In iWB
If Sht.Range("A1").Value = "Site" Then
Set iSht = Sht
End If
Next Sht
If iSht Is Nothing Then
MsgBox "We're doomed to fail, we're never gunna make it"
Exit Sub
End If
Set Cel = iSht.Range("A2")
Set iRng = iSht.Range(Cel, Intersect(Cel.End(xlDown).EntireRow, Cel.End(xlToRight).EntireColumn))
Set Cel = RawSht.Range("A2")
Set R = RawSht.Range(Cel, Intersect(Cel.End(xlDown).EntireRow, Cel.End(xlToRight).EntireColumn))
R.ClearContents
Set OutR = RawSht.Range(Cel, Cel.Offset(iRng.Rows.Count - 1, iRng.Columns.Count - 1))
OutR.Value = iRng.Value
Exit Sub
NoTotLabAllocation:
MsgBox "The ReportsMaster.aspx workbook isn't open, Please export the Total Allocation Report and try again"
End Sub