Copying Data from a ASPX file opened in Excel

[Jeffrey Mahoney]

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.

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

 

[Jeffrey Mahoney]

Bump. I just don't know how to reference it as a workbook or a worksheet.