Copying Data from a ASPX file opened in Excel

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
3,212
Office Version
  1. 365
Platform
  1. 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.

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
 

Excel Facts

How to total the visible cells?
From the first blank cell below a filtered data set, press Alt+=. Instead of SUM, you will get SUBTOTAL(9,)

Forum statistics

Threads
1,224,825
Messages
6,181,191
Members
453,021
Latest member
pingpong7117

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top