+ Reply to Thread
Results 1 to 2 of 2

HELP Getting a Compile Error: Expected End With

  1. #1
    Registered User
    Join Date
    09-27-2012
    Location
    San Jose, Cali
    MS-Off Ver
    Excel 2007
    Posts
    1

    HELP Getting a Compile Error: Expected End With

    The start Sub Getdata() is highlighted yellow and at the bottom the End Sub is highlighted blue please help I'm lost. Thanks in advance.




    Sub Getdata()

    ' Get Blend Log data from network drive and paste into Web Data worksheet

    Dim Monthmmm, Yearyyyy, fname, path, pathfname, printall
    Sheets("Web Data").Select
    Let Monthmmm = Cells(4, 1).Value
    Let Yearyyyy = Cells(3, 1).Value

    Let path = "X:\Raw Water Reports\Blend Log\Blend Log 2012\"
    fname = Monthmmm & " " & Yearyyyy & ".xlsx"
    Let pathfname = path & fname
    ' Cells(1, 1).Value = pathfname
    Workbooks.Open Filename:=pathfname, _
    Notify:=False
    Range("A1:M51").Select
    Selection.Copy
    Windows("Temporary 2012 Schematics3.xls").Activate
    Sheets("Web Data").Select
    Range("B1").Select
    Selection.PasteSpecial Paste:=xlPasteValues
    ' Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    ' :=False, Transpose:=False
    ' ActiveSheet.Paste
    ' Close Blend Log workbook; user is prompted to remove data from clipboard so user
    ' should click "NO" to remove data from clipboard; should refine in future.
    Windows(fname).Activate
    ActiveWindow.Close

    ' Get ALERT data from internal webpage and paste to Web Data worksheet
    ' http://alert3.valleywater.org/opsplandata.html
    ' (External webpage address http://alert.valleywater.org/gagestrm.html)

    Range("AE4").Select
    With Selection.QueryTable
    .Connection = "URL;http://alert.valleywater.org/sgi.php"
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False


    Range("AN4").Select
    With Selection.QueryTable
    .Connection = "URL;http://alert.valleywater.org/rgi.php"
    .WebSelectionType = xlEntirePage
    .WebFormatting = xlWebFormattingNone
    .WebPreFormattedTextToColumns = True
    .WebConsecutiveDelimitersAsOne = True
    .WebSingleBlockTextImport = False
    .WebDisableDateRecognition = False
    .WebDisableRedirections = False
    .Refresh BackgroundQuery:=False


    ' Print out all each water supply recharge system schematic
    ' will want to improve by adding a yes/no box to the code below

    Let printall = InputBox("To print all Enter: Yes")

    If printall = "Yes" Then
    Sheets("Guadalupe").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Stevens Creek").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Los Gatos").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("South County").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Pennitencia").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Sheets("Coyote").Select
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    End If


    End With
    Sheets("Web Data").Select
    Range("A5").Select

    End Sub


    Sub Print_single_sheet()
    '
    ' Print_single_sheet Macro
    ' Macro recorded 09/23/2006
    ' Print each individual system worksheet

    '
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, Collate:= _
    True
    End Sub

  2. #2
    Forum Moderator vlady's Avatar
    Join Date
    09-22-2011
    Location
    Philippines - OLSHCO -Guimba-Nueva Ecija
    MS-Off Ver
    2013
    Posts
    4,337

    Re: HELP Getting a Compile Error: Expected End With

    Welcome to the forum Turtles62

    Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here
    I think people forget the word "THANK YOU!!!!" Do you still know it???

    There is a little star ( ADD REPUTATION ) below those person who helped you. Click it to say your "PRIVATE APPRECIATION TO THEIR EFFORT ON THEIR CONTRIBUTIONS "

    Regards,
    Vladimir

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1