• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Newly Intsalled Windows 7 With Excel 2003 - VBA Compile Error ???

Q

Qcan

New Member
Hi,


Yesterday I releuctantly had to reinstall Windows due to ongoing slownees. Everything went well. The last time I did this was a couple of years ago. The problem I am going to describe was the same. I somehow figured it out through trial and error. This time... I have ran out of trials !! I am certain that there is a simple solution.


Problem :


I have a nice macro that was created by a third party that essentially scrapes one particular website that requires a password. The issue is that since reinstalling Windows and Excel 2003 (again), I am getting the same error when trying to run the macro. The error is "Compile error: Can't find project or library". The code stops at "format". As I remember from previous instalations - the issue is date formatting. I looked at the code and tried different combinations by modifying the date format in Window's "region and language" tab in settings. I have change both the "short date" and "long date". Nothing is working. I think I am barking up the right tree... but I'm pretty much at a dead end.


Below is the code. Any suggestions would be greatly appreciated.


Sub RunQueries()

Dim newsheet As String

Dim Xfrom As Long

Dim Xto As Long

Dim QryString As String

Dim StartCell As String

Dim MembID As String

Dim RRow As Integer


Range("F7").Select

RRow = ActiveCell.Row

' Starts going down the list in the MainMenu

Do While ActiveCell.Offset(0, 0) > " "


MembID = ActiveCell.Offset(0, 0).Value

Xto = ActiveCell.Offset(0, 2).Value

Xfrom = ActiveCell.Offset(0, 1).Value

' Adds new sheet, names it and put on date headings

Sheets.Add

ActiveSheet.Name = MembID

Range("A1").Select

ActiveCell.Offset(0, 0) = "From Date:"

ActiveCell.Offset(0, 1) = Xfrom

ActiveCell.Offset(0, 1).Select

Selection.NumberFormat = "mmmm d, yyyy"

ActiveCell.Offset(1, -1).Range("A1").Select

ActiveCell.Offset(0, 0) = "To Date:"

ActiveCell.Offset(0, 1) = Xto

ActiveCell.Offset(0, 1).Range("A1").Select

Selection.NumberFormat = "mmmm d, yyyy"


'http://wagerline.com/sportscontests/picksByDate.aspx?date=03/13/2006&ur=9588&contestID=0&sportID=0


StartCell = "A5"

' Loops through query to put data on new sheet

Do While Xfrom <= Xto

' QryString = "URL;http://wagerline.com/sportscontests/picksByDate.aspx?date=" & Format(Xfrom, "mm/dd/yyyy") & "&ur=" & MembID & "&contestID=11081&sportID=5"

QryString = "URL;http://www.wagerline.com/sportscontests/picksByDate.aspx?date=" & Format(Xfrom, "mm/dd/yyyy") & "&ur=" & MembID & "&contestID=19684&sportID=5&t=0"

'Range("d1").Value = QryString

With ActiveSheet.QueryTables.Add(Connection:= _

QryString, _

Destination:=Range(StartCell))

.Name = "Web qry"

.FieldNames = True

.RowNumbers = False

.FillAdjacentFormulas = False

.PreserveFormatting = True

.RefreshOnFileOpen = False

.BackgroundQuery = True

.RefreshStyle = xlOverwriteCellsr />
.SavePassword = False

.SaveData = True

.AdjustColumnWidth = True

.RefreshPeriod = 0

.WebSelectionType = xlSpecifiedTables

.WebFormatting = xlWebFormattingNone

.WebTables = "1"

.WebPreFormattedTextToColumns = True

.WebConsecutiveDelimitersAsOne = True

.WebSingleBlockTextImport = False

.WebDisableDateRecognition = False

.Refresh BackgroundQuery:=False

End With

Range(StartCell).Select

If ActiveCell.Offset(1, 0) <> "No result details for this date" Then

ActiveCell.Range("A1:H1").Select

Selection.Delete Shift:=xlUp

ActiveCell.Offset(0, 7).Range("A1").Select

Do While Not IsEmpty(ActiveCell.Offset(0, -6))

ActiveCell.Offset(0, 0) = Xfrom

Selection.NumberFormat = "mmmm d, yyyy"

ActiveCell.Offset(0, 1) = MembID

ActiveCell.Offset(1, 0).Range("A1").Select

Loop

ActiveCell.Offset(-1, -7).Range("A1").Select

Else

ActiveCell.Range("A1:H1").Select

Selection.Delete Shift:=xlUp

ActiveCell.Offset(0, 7) = Xfrom

ActiveCell.Offset(0, 7).NumberFormat = "mmmm d, yyyy"

ActiveCell.Offset(0, 8) = MembID

End If

StartCell = ActiveCell.Offset(1, 0).Address()

Xfrom = Xfrom + 1

Loop

Columns("A:h").Select

Columns("A:h").EntireColumn.AutoFit

Range("a1").Select

Sheets("MainMenu").Select

RRow = RRow + 1

Range("f" & RRow).Select

Loop

End Sub
 
Hi, Qcan!


First of all welcome to Chandoo's website Excel forums. Thank you for your joining us and glad to have you here.


As a starting point I'd recommend you to read the three first green sticky topics at this forums main page. There you'll find general guidelines about how this site and community operates (introducing yourself, posting files, netiquette rules, and so on).


Among them you're prompted to perform searches within this site before posting, because maybe your question had been answered yet.


Feel free to play with different keywords so as to be led thru a wide variety of articles and posts, and if you don't find anything that solves your problem or guides you towards a solution, you'll always be welcome back here. Tell us what you've done, consider uploading a sample file as recommended, and somebody surely will read your post and help you.


And about your question...


I haven't access to any machine running 2003 versions so I can't help you further than confirming that in 2010 versions it compiles Ok.


What you could do is go to the VBA editor, Alt-F11, then Tools, References and check if any of the enabled at top of the list says missing or something alike. If yes try to select the proper one from the unchecked items; if not please post those references here. Thank you.


Regards!
 
Qcan,


Open the Visual Basic Editor. Go to > Tools > References. Look at the top of the checked-list to see if there are any libraries labeled as "MISSING!," or something to that effect. If there are, take note post what you find here.
 
Hi Jordan,


Actually.... yes there is. One library is missing. It's called "Microsoft Windows Common Controls-2.6.0 (SP6).


I will google this... but will not do any action until you can hopefully tell me what to do.


Thanks.
 
Hmmmm.... That Common Controls library is no longer part of the new installations of Office. Try simply deselecting the checked missing library. Save. Exit Excel. Then open it up and try running the macro again.


If that doesn't work, download the library here: http://support.microsoft.com/kb/297381. Dump it into your System32 folder. Finally, go back to your VBA editor and rereference it. Then run again.


Maybe you should do that second part first? I don't know. I'm trying to see what SirJB7 is writing, but he keeps putting his head in the way.
 
@Jordan

Hi!

I was just writing a love letter to my lady. You're not gonna say you want to go on with your furtive reading, are you?

Regards!
 
Hi, Qcan!

Having a copy of the file would help a lot to determine the changes to be made. Consider uploading a sample file. Give a look at the second green sticky post at this forums main page for uploading guidelines.

Regards!
 
Hey Jordan,


Well I deselected and..... to my surprise & amazement - it now works !!!


Problem solved.


Thanks Jordan.


Thank you as well SirJB7.
 
You must log in or register to reply here.
Back
Top