×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Where is web page opened via VBA macro stored on C: drive?

Where is web page opened via VBA macro stored on C: drive?

Where is web page opened via VBA macro stored on C: drive?

(OP)
I am running this code in Excel VBA:

CODE

Sub marco1()
Application.Workbooks.Open "http://www.eng-tips.com"
End Sub

which opens this page as a read-only file.  Where is this file stored on my C: drive?  I have looked in C:\Users\Default\AppData\Local\Microsoft\Windows\Temporary Internet Files and it is empty.  I have also looked in Libraries\Documents, where the MS Excel file is saved, and I did not find it there either.  I also have looked in C:\Program Files (x86)\Microsoft Office\Office12 and I still did not find anything.

Where is this file stored?

RE: Where is web page opened via VBA macro stored on C: drive?

When I run your code in Excel,  the page opens in Excel itself as a new tab, with frame boundaries demarcating columns, and line returns demarcating rows.

That's not surprising, since Workbooks.Open is supposed to open a WORKBOOK.  It's not saved anywhere until you do a SaveAs.

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

RE: Where is web page opened via VBA macro stored on C: drive?

yes!!

RE: Where is web page opened via VBA macro stored on C: drive?

(OP)
Ok, let me explain my problem a little better - the code opens a web page that contains key words.  These key words are then verified.  If the key words are not found, the excel workbook deletes all the sheets and saves.  This is one way to prevent each file from being used without authorization.  The file could then be sold to be used on a licensed basis or some other way requiring authorization.

My concern is in this section of code:

CODE

'OPEN INTERNET WINDOW AND REVIEW AUTHORIZATION
On Error GoTo internet_access_required:
Application.Workbooks.Open (Web_Page)

On Error GoTo not_authorized:
Cells.Find(What:=authorization_key_word, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
'close Web_Page
Application.ActiveWorkbook.Close savechanges:=False

Application.DisplayAlerts = True
Exit Sub

Sometimes the page opens slowly.  Is it possible for the user to find that instance of the web page on the C: drive?  Is it possible that the user interrupts the macro prior to running:

CODE

'close Web_Page
Application.ActiveWorkbook.Close savechanges:=False
   
I know that Excel has an auto-save feature.  Where is the location of the file that is automatically saved?

This is the complete code.  Web_Page, authorization_key_word, WBook_Password and Sheet_Password are strings defined elsewhere in VBA.  The webpage is a blog that only I can access.

CODE

Sub VERIFICATION()

Application.DisplayAlerts = False

'OPEN INTERNET WINDOW AND REVIEW AUTHORIZATION
On Error GoTo internet_access_required:
Application.Workbooks.Open (Web_Page)

On Error GoTo not_authorized:
Cells.Find(What:=authorization_key_word, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        True, SearchFormat:=False).Activate
'close Web_Page
Application.ActiveWorkbook.Close savechanges:=False

Application.DisplayAlerts = True
Exit Sub

'===============
'goto NOT AUTHORIZED
not_authorized:

'delete all sheets
ActiveWorkbook.Unprotect Password:=WBook_Password
Sheets.Add Before:=Sheets(1)
While Sheets.Count > 1
Sheets(Sheets.Count).Visible = True
Sheets(Sheets.Count).Unprotect Password:=Sheet_Password
Sheets(Sheets.Count).Select
ActiveWindow.SelectedSheets.Delete
Wend

Sheets(1).Protect Password:=Sheet_Password
ActiveWorkbook.Protect Password:=WBook_Password
'save and close this workbook
Application.ActiveWorkbook.Save

MsgBox prompt:="Use not authorized." _
    & "  This application will now close.", _
    Buttons:=vbCritical, _
    Title:=verification_title
Application.ActiveWorkbook.Close

Application.DisplayAlerts = True
Exit Sub
   
'===============
'goto Internet access required to run this program
internet_access_required:
MsgBox prompt:="Internet access is required to run this program." _
        & "  This application will now close.", _
    Buttons:=vbCritical, _
    Title:=verification_title
'close this workbook
Application.ActiveWorkbook.Close savechanges:=False
Application.DisplayAlerts = True
Exit Sub

End Sub

RE: Where is web page opened via VBA macro stored on C: drive?

Still not clear what you're asking.  If you interrupt it before it closes, the page is sitting there in Excel as Application.ActiveWorkbook.  You can then save it to wherever you want.

The Autosave goes to whatever folder is the default, unless current directory is changed by the user during the time Excel is opened.
 

TTFN
FAQ731-376: Eng-Tips.com Forum Policies

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources