It gives me an error:
Calculate method of Range class failed
And highlights c.Calculate
Posts by Javy Dreamer
-
-
My workbook is heavy on calculations (many cells use VBA formulas) so it recalculates all cells with formulas each time i change a cell. As of now is around 450 cells so u can imagine excel gets slow. Is there a way to control that? I managed to make it recalc before save but isn't there a way to tell the sheet to recalc values via VBA? I need to be able of selecting wich cells i want to be calculated automatically and wich not. Is this possible???
-
Excuses from the lack of clarity. The macro is ran in Excel but will work on a word document. I'm on WIN 2000, Office 2003 just in case.
-
<pre>
This is the code in Word:
Sub Macro1()With Selection.Find
.Text = "$%Registrador%$"
.Replacement.Text = ":O"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Find.Execute Replace:=wdReplaceAll
End SubAnd this the one in EXCEL:
Sub replace(doc As Word.Document)
With doc.Content.Find
.Text = "$%Registrador%$"
.Replacement.Text = ":O"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute replace:=wdReplaceAll
End With
End SubFrom excel i can't find the selection thingy, the closest was Content.Find. Any ideas?
</pre> -
The problem i've found so far is 'dividing' the different pages of my workbook when creating the csv in order to be able to restore the original from the csv file (that's really a backup)
-
Hmm... never thought of that. I'll try it out and post here results later. Thanx
-
All of it as a backup. :biggrin:
-
It saved something in the database but isn't what I wanted. Saved this:
"Long binary Data" as text i guess. When i try to open the object it raises an error sayin OLE and Active X couldn't open the object. -
I posted the problem in this link and then realized it might go in this forum:
http://www.ozgrid.com/forum/viewthread.php?tid=9739 -
<pre>I thought this would be easy but it doesn't. This is the code I thought will work but:
sql = "UPDATE Seccion SET Seccion.[Registro] = '"
sql = sql & ThisWorkbook & "';"
cn.Execute (sql)In my opinion this was the reasonable way to go but it says: "object doesn't support this property or method". Any idea?
</pre> -
I'm on Win 2000 and Office 2002. If the code should work at least it doesn't in my comp even with the protection u suggested. The weird thing is that the replace macro works wonders within Word. Isn't there a way to call that macro? The problem with that aproach is that it won't give the versatility I wanted. You know write any type of letter with an specific format.
-
<pre>"Error 5: Invalid procedure call or argument" and highlights ths line:
.Text = "$%Registrador%$"Here's the whole code just in case:
Sub OpenAndReadWordDoc()Dim wrdApp As Word.Application
Dim wrdDoc As Word.Document
Dim newwrdDoc As Word.Document
Dim tString As String, tRange As Word.Range
Dim p As Long, r As Long
Set wrdApp = CreateObject("Word.Application")
'wrdApp.Visible = True
wrdApp.Visible = True
Set newwrdDoc = wrdApp.Documents.Add
Set wrdDoc = wrdApp.Documents.Open("E:\UI_Database\Documents\Términos\200430\Carta Reintegro.doc")
' example word operations
With wrdDoc
For p = 1 To .Paragraphs.Count
Set tRange = .Range(Start:=.Paragraphs(p).Range.Start, _
End:=.Paragraphs(p).Range.End)
tString = tRange.Text
tString = Left(tString, Len(tString) - 1)
newwrdDoc.Content.InsertAfter tString
newwrdDoc.Content.InsertParagraphAfter
' exclude the paragraph-mark
' check if the text has the content you want
MsgBox tString
Next p
.Close ' close the document
End With
Call replace(newwrdDoc)
wrdApp.Quit ' close the Word application
Set wrdDoc = Nothing
Set wrdApp = Nothing
ActiveWorkbook.Saved = True
End SubSub replace(doc As Word.Document)
With doc.Content.Find
.Text = "$%Registrador%$"
.Replacement.Text = ":O"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute replace:=wdReplaceAll
End With
End SubAs I said the only problem/error arises when I try the replace sub
</pre> -
<pre>I'm creating a Word document from EXCEL and i don't have problems in that. The real problem is using replace. Always creates an error in Excel and it must be closed. I'm looking for strings in the form of $%'word'%$.
Here's the code:
Sub replace(doc As Word.Document)
With doc.Content.Find
.Text = "$%Registrador%$"
.Replacement.Text = ":O"
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
.Execute replace:=wdReplaceAll
End With
End SubI also need some sort of protection just in case the word i'm looking isn't there. Any idea?
</pre>
-
The real problem is how i get the content of each entry in the recordset individually
-
Manged to pull it out. Copied the addresses to a hiden column and populate from there. Thanx anyways
-
<pre>I managed to save the nicks in a database but I'm having trouble retrieving them. I've tried to get the number of rows from
the result set but it doesn't work. Here's that part of code:
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Integer, sql As String
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\UI_Database\Registro Database.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
Dim i As Integer
sql = "SELECT Nick FROM Email;"
Set rs = cn.Execute(sql)
r = rs.RecordCount
MsgBox r
r is always -1 even wth 450+ entries in the database. I need to know the size of the record set to control a for loop unless u have a better
idea....
I'll keep looking in the forus for info I know it should be somewhere..... </pre> -
Quote
Originally posted by Andy Pope
Not sure you can get round the permission dialog.To add from cells you can use something like,
For each rngCell in Range("A1:A20")
.Additems rngCell
NextCheers
Andy
I managed to save the nicks in a database but I'm having trouble retrieving them. I've tried to get the number of rows from the result set but it doesn't work. Here's that part of code:
[vba]
Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Integer, sql As String
' connect to the Access database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\UI_Database\Registro Database.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
Dim i As Integer
sql = "SELECT Nick FROM Email;"
Set rs = cn.Execute(sql)
r = rs.RecordCount
MsgBox r
[/vba]
r is always -1 even wth 450+ entries in the database. I need to know the size of the record set to control a for loop unless u have a better idea....</pre> -
It works well. The only prob is the warning from Outlook asking for permission for using the list. Any way around it? BTW I'll like an idea on how to populate the dropdown. Not really how to populate it since i've done it before, what i really mean is how to use the Cells object with it?
-
Quote
Originally posted by Derk
Its a puzzle! What happens with
Sub Button2_Click()
MsgBox Chr(65)
End Sub
<pre>
Still same error, can't find object or library. I even deleted the button and created a new one to make sure the method was for the right button, but no luck</pre> -
Is there a way to do this in order to fill a dropdown list with the results?