Posts by Miffo

Re: Restricting Number Of Iterations In Do While Loop

Hello!

You can alsways use the Exit For command to just jump out of the for-loop.
Another option is this:

Dim cnt As Long 
cnt = 1 'The first row


Do While (Not Cells(cnt, 1).Value = Empty) And (cnt < 96) 'until either a cell is empty or we are at row 96. This means we have checked row 95.
  'do what you want here...
  cnt = cnt + 1 'Increasing the row
Loop

Re: Select By Column Index

Or if you want to use a variabel instead (for a loop or something), just remove the comma from your own code:

Dim Counter as Integer


Counter = 157


Columns(Counter).Select

Re: Copy A File To Desktop When Users Are Different

Hello!

I'm not sure if the solution your looking for is quite so simply as I imagine, since the answer more or less is in the post just 3 posts down (at the moment of writing).

In windows the desktop is specific to the user, and therefore it its not important what computer they are sitting on, but what login they have in windows. Try this:

UID = Environ("Username")
ActiveWorkbook.SaveAs Filename:= _
    "C:\Documents and Settings\" & UID & "\Desktop\Book1.xls", FileFormat _
    :=xlNormal, Password:="", WriteResPassword:="", ReadOnlyRecommended:= _
    False, CreateBackup:=False

That should save the file as Book1.xls on the current desktop.

Hope this helps you....

Re: Seeing a webquery

Hello!

I just wanted to say I solved it myself.
The problem was that I couldnt acces the querytable by using activesheet for some reason, but the following worked:

Sheets("Sheet2").Select
Cells(1,1).Select
if Selection.QueryTable.Refreshing = True then
  call SaveDocument
end if

Well, thanks anyway for being here even if no one had had the time to look at this yet...

Hello everyone!

Now I might have a very simple problem, but I cant seem to find a solution to it.

I have a workbook with three diffrent sheets (and more, but they are not relevant here), each with its own webquery.

After a query has been done I check with the OnChange-event, and that works fine.

My problem is that I want to save the document after the queries has been done, but if I save it after the first one is done, the second and third queries are aborted.
Is there any way to see if a query is being refreshed or not.
If I can see that a query is refreshing, I can tell the macro not to save, and when the last one is done it will save there instead (I have no idea if its going to be the first, second or third query thats being refreshed last, as they take diffrent time to perform).

I was thinking of something similar to:

Sheets("Sheet2").Select
if not ActiveSheet.QueryTable.Refresh then
  Call SaveDocument
end if

Now, this doesnt work since the querytable doesnt have the refresh-propery to cehck, but its something like this I would like.
Anyone know if there is anyway to do this?

Kind regards
/Henrik

Re: Passing a variable on OnChange-event

Thanks!

That solved my current problem...

Hello!

I've been trying to pass a variable from the OnChange-event on one sheet to a macro located in module one, but it seems I cant (or havent figured out how anyway). Anyone knows how to do it?
The code here give me the error "RefreshSetup(WhatSheet) cannot be found"

Alternativly, maybe someone can come up with a better solution on my entire problem. 3 sheets with webquery. All should be handled in the same way (copy-pasting only), but source and destination-sheet differs.
My big problem is if two queries are done at the same time, they conflict with eachother, and data from one query is copied on to the wrong sheet.

Ex: Sheet 1 is running, finish, and sees the onchange-event, runs that macro.
In the middle of the macro, Sheet 2 is done and reacts to the onchange-event and start running that which will change the sheet I'm currently working on, the data from sheet 1 will be copied to sheet2 destination instead of sheet1's destination. (instead of passing the sheetname, I just select it in the OnChange-event)
Therefore I made a public variabel that stops the second one from running if the flag is raised, and set an ontime event so it will wait with that one.
Now I need to pass which sheet its supposed to work on, and was thinking I'll pass it as a simple string.

OnChange (looks the same on all sheets, except sheetnames)

Private Sub Worksheet_Change(ByVal Target As Range)


If Not Running Then
  RunWhen = Now + TimeSerial(0, 0, 1)
  WhatSheet = "HtmlTmp"
  Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
Else
  RunWhen = Now + TimeSerial(0, 0, 3)
  WhatSheet = "HtmlTmp"
  Application.OnTime RunWhen, "Module1.RefreshSetup(WhatSheet)"
End If
End Sub

Module1:

Sub RefreshSetup(ByVal WhatSheet As String)
  
  Running = False
  
  Sheets(WhatSheet).Select
  
  Select Case WhatSheet
    Case "HtmlTmp"
      Sheet = "html"
    Case "Html2Tmp"
      Sheet = "html2"
    Case "Html3Tmp"
      Sheet = "html3"
  End Select
  
'The ErrorInQ is a custom function to see if the data from the query was ok or not
  If Not ErrorInQ Then
    Cells.Select
    Selection.Copy
    Sheets(Sheet).Select
    Cells.Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
      :=False, Transpose:=False
  End If
  Running = False
End Sub

Re: Bug in runtime

Absolutly, but what I can understand its the code I posted that the problem.
The workbook consists of 4 sheets (more actually, but they are not important here as they are never thouched my the macro).
Its Calculator (the only visible sheet from the beggining)
TmpSheet (where the webquery is run)
html (where I dump the result of the webquery if I get the right figures)
html2 (where I dump only a part of the webquery on the same criteria as the sheet html).

This is in the worksheet TmpSheet:

Private Sub Worksheet_Change(ByVal Target As Range)
  SetupRefresh
End Sub

This is another module:

Sub SetupRefresh()
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  CurrentBook = ActiveWorkbook.Name
  'If I debug from here, the CurrentSheet will get the value Calculator
  CurrentSheet = ActiveSheet.Name
  'If I start debug here, the CurrentSheet will get the value TmpSheet
  Workbooks("disclosed filename.xls").Activate
  Sheets("TmpSheet").Visible = -1
  Sheets("html").Visible = -1
  Sheets("html2").Visible = -1
  'ErrorInQ is a function I buildt that just looks at the webquery to see if its the right values or not.
  If Not ErrorInQ Then
    'Here I just do a lot of copying around and things
  End If
  Sheets("TmpSheet").Visible = 2
  Sheets("html").Visible = 2
  Sheets("html2").Visible = 2
  Workbooks(CurrentBook).Activate
  Sheets(CurrentSheet).Select
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
  Application.EnableEvents = True
End Sub

So, As I said before. The problem appears when the webquery has been done in the sheet TmpSheet and the Change-event triggers on that sheet. In Runtime when it comes to the line CurrentSheet = ActiveSheet.Name it says its TmpSheet that the active one. I have no problem understanding that, but in break mode and stepping my way through the code it says its the sheet I'm currently looking at (called Calculator), and thats just confusing me. I should get the same result in debug as in runtime, unless I'm working with time or anything similar as far as I know.

PS: Sorry for all the spelling mistakes. I'm a bit hungover today, hopefully it will pass as the day go on...

Hello!

Sorry if the title wasnt clear enoguh. I just cant think of any more specific way of titling it at the moment.

The bug I'm speaking of has come up a few times for me in diffrent situations.
Its seems as Excel isnt updating what it does properly according to the macro runtime. An example:

Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  Application.EnableEvents = False
  CurrentSheet = ActiveSheet.Name
  CurrentBook = ActiveWorkbook.Name

This is just the first few rows of my macro. I want to be able to run my macro, and then leave the user at the workbook and sheet he/she was working on before.
If I let it run, the CurrentSheet will get a sheetname that is hidden, and NOT active. I havent even started to touch it yet with my macro, but a webquery is run on it, and the macro is run on the Change-event on that sheet.
If I set up a breakpoint on the line before I give the variable CurrentSheet its value, I get the sheetname that is currently active.
I.e. During runtime Excel does not update itself before it continues. In debug mode it does.

This is most annoying, since my code should work after what I know, and it DOES work in debug mode, but not in runtime.
Has anyone else come across this, and do you have a solution for it?

Kind regards
/Henrik

Hello!

Ever since I found this forum I seem to run in to more and more trouble all the time...

The scenario this time is that I have a webquesry that runs from an (at best) unstable server.
Every now and then I only get bogus-data, and I can seperate that data from the real data I want.
The problem is that if I get wrong data, I want the old data (from a correct query) to overwrite the new data (from an incorrect webquery).
I have an eventhandler on Change for the sheet that has the query, and it works perfectly. On the eventhandler I check to see if the data is correct or not and if right I copy the new data to overwrite the old data, otherwise the opposite; copy the old data to overwrite the new data.
Here is the problem. If the new data is wrong, and overwritten by the new data, I have to set up the webquery again on that sheet, but it tries to update the sheet automatically then. How do I avoid this?
I dont want it to update again for another 7 minutes.

Due to the security I dont want to show the real URL. It is however an internal server, so it wouldnt help you to see it anyway. Sorry for that...

Sub CheckIfError()


  Sheets("Sheet3").Select
  TmpStr = Cells(1, 1).Value
  TmpStr = Replace(TmpStr, Chr(34), "'")
  'Bogus data gives this line in the first cell
  If TmpStr = " <font face='Arial' size=2>" Then
    Sheets("BackupSheet").Select
    Cells.Select
    Cells.Copy
    Sheets("Sheet3").Select
    Cells.Select
    ActiveSheet.Paste
    Cells(1, 1).Select
    SetupRefresh
  Else
    Sheets("Sheet3").Select
    Cells.Select
    Cells.Copy
    Sheets("Backupsheet").Select
    Cells.Select
    ActiveSheet.Paste
    Sheets("Sheet3").Select
    Cells(1, 1).Select
  End If
  
End Sub




Sub SetupRefresh()


    With ActiveSheet.QueryTables.Add(Connection:= _
        "URL;http://URL/Disclosed/due/to/security", _
        Destination:=Range("A1"))
        .Name = "Telephony_index1.asp?Account=BNQ"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 7
        .WebSelectionType = xlEntirePage
        .WebFormatting = xlWebFormattingNone
        .WebPreFormattedTextToColumns = True
        .WebConsecutiveDelimitersAsOne = True
        .WebSingleBlockTextImport = False
        .WebDisableDateRecognition = False
        .WebDisableRedirections = False
        .Refresh BackgroundQuery:=False
    End With
End Sub

Re: Cells of object _Global fails after running a sub.

Just to let everyone know.
I think I found the solution myself.
I just used Range instead of Cells, and it seems to work:

If Not (Range("A2").Value = Empty) And Range("A4").Value = Empty And Range("A6").Value = Empty Then
    'When an agent is chosen, set which dates are going to be shown.
    SetDateRange
  End If

instead of:

If Not (Cells(1, 2).Value = Empty) And (Cells(1, 4).Value = Empty And Cells(1, 6).Value = Empty) Then
    'When an agent is chosen, set which dates are going to be shown.
   SetDateRange
  End If

Edit:
Just saw your post when I've posted this.
Thanks for the advice, but that wasnt the problem. The problem was that it worked when the sub ShowChart wasnt run, but after it was run, I got the error.
However, after changing till Range instead of Cells it works.

Thanks anyway...

Hello everyone!

This time I have a new problem I cant get my head around. The scenario is this:
I have a compilation of data on all employees (named agents in here). On one sheet I will show graphs on diffrent figures for a specific agent.
They choose which agent from a validation box in the cell 1,2 (range A2).
When they have done that, I make a new list for the daterange and Cell 1,4 and Cell 1,6 (Range A4, Range A6) are validationboxes showing that list.
The cells are empty to begin with.

When all validationboxes are filled, I do the graphs, and here comes the problem. After doing the graphs I get an errormessage on saying that "Method 'Cells' of object '_Global' failed" when it comes to the if-statement that has the Daterange-call in it.

Ie: I dont get the errormessage when it DOESNT do the ShowCharts-sub, but after it has done that, I do get the errormessage.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)


  Application.EnableEvents = False
  Application.ScreenUpdating = False


  'Cell 1,2 = Validation-box based on a list of agents
  'Cell 1,4 = Startdate for the data to be picked for the graphs.
  'Cell 1,6 = Enddate for the data to be picked for the graphs.


  If Not (Cells(1, 2).Value = Empty And Cells(1, 4).Value = Empty And Cells(1, 6).Value = Empty) Then
    If Cells(1, 6).Value > Cells(1, 4).Value Then
      If ActiveSheet.Shapes.Count = 3 Then
        'Deleting the existing graphs, so new ones will take their place.
        ActiveSheet.Shapes(3).Delete
        ActiveSheet.Shapes(2).Delete
      End If
      'Get the information from diffrent sheets, and put the together for a graph.
      ShowCharts
    End If
  End If
  
  'This is where the error occurs, after running the ShowChars-sub above.
  If Not (Cells(1, 2).Value = Empty) And (Cells(1, 4).Value = Empty And Cells(1, 6).Value = Empty) Then
    'When an agent is chosen, set which dates are going to be shown.
    SetDateRange
  End If
  
  Application.EnableEvents = True
  Application.ScreenUpdating = True


End Sub

Re: Error in runtime first go, no error in debug or second go

Ok, I understand what you mean now.
I tried that, but it doesnt work.
I cannot use the manual steps you wrote about, since it works then, and always have. Everytime I try to set up a stop and step my way through the code it works perfectly, which means that it wouldnt matter if I recalculate it manually or not.
The Calculate-code didnt do the trick though.

It works perfectly when I only close that workbook.
I get the same error, but further down in the code now, but now I know how to circumvent it anyway, so I just have to recode it a bit and it should work.

Thanks alot for the help.
Couldnt have done it without your advice and wisdom...

Re: Error in runtime first go, no error in debug or second go

Hello!

Sorry for not replying. Been on vacation, and the last thing I wanted to do was to think about Excel, reporting or anything like that...

Quote

Have you tried forcing a calculation in between setting the formula & obtaining the value?

I'm not entirly sure what you mean by that.
Do you mean a calculation on the variable TmpDate, or any calculation at all? (i.e. ex. R = I + 1, or whatever)

I did try to open the sheet temporarily, and seems it works. I'm not getting the same errormessage, and the macro continues, so that seems to be the solution on this problem.
On the other hand, since its in a loop, somewhere, right before the end of the loop it closes my workbook too, so I get a errormessage because of that...will investigate on that problem myself though.

I just want to thank you for your patience with me, and for coming up with the solution. It helps me a lot! I really appreciate all the help you have given me!

Kind regards
/Henrik

Re: Error in runtime first go, no error in debug or second go

Hello!

Thanks for the tip, but I tried that. I aborted the script after about 1.3 million iterations...(about half a minute or something like that)

I sat up a counter to count how many times it would do that...Sadly it doesnt help...

Thanks anyway...

Re: Error in runtime first go, no error in debug or second go

I am very sure that my variables are storing valid values.
I#ve tried them in a couple of diffrent ways. First is just by breaking the code right after they are used, and look at the values from there (hovering with the mouse above the variable), and they contain exactly what I want them to contain. Second, I tried to copy them directly into a cell from the code, which yields the right result.
Third, I copied the values I set directly into the ...value = "=http://... and so on, and it works.
Every time I run the macro I print it out in the cell A1, and the correct value is shown there, which I can only conclude to that the variables are correct, since I otherwise would get an errormessage in the cell.

I'll try removing the CStr-command. I guess that its not nessecary if your using & instead of + when concating strings.

What I'm curious about thou is your syntax of building the full string...
Isnt only the filename supposed to be within brackets?
My variable path consists of both the path and the filename.
doing:

Range("A1")= "'[" & Path & "]" & Sheet & "'!B" & Y

would in that case mean that it will look like this:
'[http://123.132.123.123/path/path/[filename.xls]]sheetname'!B8
and wouldnt that mean I would get a faulty path to the file?
My variable path looks like this:

Path = "'http://123.132.123.123/path/path/[filename.xls]"

so I shouldnt have to use the brackets within the Range("A1")-command, or am I wrong here?
Since the filename is in the string Path, even if I would remove the brackets from it I would still get the result:
'[http://123.132.123.123/path/path/filename.xls]sheetname'!B8
unless I break it down even more and have a seperate variable for the filename.

As I know it you have to have the full path to the document if the document isnt open, right?
If the document is open it would suffice with only the filename.

PS: I didnt add the url-brackets if it still there. I've been trying to edit it out, but the forum adds it itself if it find what looks like a valid url I suspect.

Re: Error in runtime first go, no error in debug or second go

I have no idea why its there twice, I checked my strings about 10 times trying to figure it out, but it happend when I did as Iswanson said:

Range("A1").Formula = "='[" & Path & "]" & Sheet & "'!B" & CStr(Y)

With my original code it looked fine (including the single quotes).

I'm actually becomming more and more concerned its some setting in my computer that makes this error because the documentname (as also appeared twice with the above code) also came up twice, and the documentname is a piece of the full string, not a string by itself, so logically the whole path would have appeard again, but only the part with the documentname came up twice, and the sheetname (which I, although I checked, I could remotly write off as a loop-error, adding it twice to the complete string).

Just to try I did this:

Range("A1").Value = "='http://123.123.123.123/path/path/[docname.xls]sheetname'!$B$8"
TmpDate = Range("A1").Value

but with the real path and sheetname so I have it hardcoded so to speak. To see if I made anymistake I just copied it and pasted into a cell directly and got the result I wanted, but it still generated the exact same error when I run it.
Still doesnt work on the first run. Worsk fine when I step into the code line by line, and it works perfectly if I just run the macro again without closing the document.
I get the runtime error 13, Type Mismatch.
TmpDate get the value "Error 2023".
The cell A1 has the right value though; "1 Jul 2006 "

Re: Error in runtime first go, no error in debug or second go

Well, you and me both. I'm totally confused about the error...

Because of security I'm not in liberty to give the full path of my documents (actually I dont know if I am or not, but I dont want to take any risks and getting fired because of it).

I have a couple of lines of codes that looks like this:

Path = "'http://123.123.123.123/the actual path/[filename.xls]"

and later I have a loop that reads in values from my spreadsheet, and since the values are the same as the sheet-names on the intranet-document I do this:

Sheet = UCase(Range("A" + CStr(X)).Value) + "'!"

(X is the counter ranging from 5 up to maximum of 22 since, but reads only if the cells isnt empty)

wich means that the full path would look something like this:
'http://123.123.123.123/path/path/[document.xls]SHEETNAME'!B8
(I've changed the actual IP-adress, path, documentname and sheet for above stated reasons)
This path works as far as I know.When I run my macro and get the error message I can see the value I should get in the cell A1. Its when reading that value I get the error...

I did however exactly what you suggested, but sadly it doesnt work. Of some strange reason it gives a #REF and when looking at the value of the cell it looks like this:
='[http://123.123.123.123/path/path/documentname.xls]sheetname]documentname]sheetname'!B8

I think I went over it 10 times before I concluded that I didnt do that...its something Excel did by itself...

The same error still appears though...

Thanks for the help anyway...its greatly appreciated...

Re: Error in runtime first go, no error in debug or second go

Thanks for the answer, and specially the elimination of my loops. Been trying to find a better way, but havent had the time to go through VBA-string handling routines properly.
My whole macro could probably be cut in half if I knew all the exact routines, instead of rebuilding them in other ways with my own code...
As I said, it might not be good programming, but it should work...

However, it doesnt work forcing it to a string, and TmpDate is set as a String in the beginning of the macro.
When trying the code

TmpDate = CStr(Range("A1").Value)

it gives the exact same result, EXCEPT that I dont get the error message.
It runs the macro as if it works, but the TmpDate wont get any value from the cell. When I did a break on the line under it and looked at the value of TmpDate I see "Error 2023", the same as before.

It still works though the second time I run the macro without closing Excel in between, and still works to debug my self through the code, if I break before the error appears. Its ONLY the first time the code is run in runtime that I get this errormessage.

Hello!

I've got a slight problem I have no idea how to deal with here.
The code thats generating the error is the following:

[FONT=Courier New]For Y = 8 To 131
TmpDate = Empty
Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
TmpDate = Range("A1").Value
 
For Langd = 1 To Len(TmpDate)
TmpChar = Mid(TmpDate, Langd, 1)
If Not TmpChar = " " Then
TmpComp = TmpComp + TmpChar
End If
Next Langd
 
TmpDate = TmpComp
TmpComp = Empty
 
If Len(Dag) = 1 Then
Dag = "0" + CStr(Dag)
End If[/FONT]

Macro continues before the Next-statement...

(it might not be good programming, but I think it should work).
A little explanation to the code.
I have a spreadsheet located on the intranet that has values I need in my spreadsheet. These figures are sorted by date, so I search for the date to find the right figures.
The line Range("A1").Value = "=" + Path + Sheet + "B" + CStr(Y)
works prefectly. I can see the value in my spreadsheet in the cell A1. its the next line that causes the error "Type mismatch".
The value I get from the intranet spreadsheet looks similar to this:
"1 Jul 2006 " (note all the spaces).
What I do is just run it through a loop and cut of all spaces so it will look like this: "1Jul2006" to be sure I dont miss a space or anything.

Now to the funny part.
The error only happens at runtime on the first go.
If I chose to END the macro then and there, and then run it again from the top it works perfectly.
If I set a stopsign to debug the code, it works perfectly on the first go too.
Can anyone explain to me why that happens??

I know enough about programming to handle my syntax errors and so on, but I cant see anything wrong in this one...

Please note that this isnt the the entire code.
I'm building a report on 12 diffrent spreadsheet. The main macro is almost 2000 rows of code, and it call other Subs too (because of limitations in the VBA-editor. A macro cant to be too big), so its impossible for me to post the entire macro...