PDA

View Full Version : Solved: Is it possible to store information in worksheets, workbooks?



TanerPro
11-16-2006, 07:31 AM
Hello,
I am using AutoCAD and i can use Xrecord command to store any information inside drawings. This is very useful instead of asking the user to open settings file for each drawing, you can get settings from the drawing directly. Now, is there anything similar in Excel, that we can store and get any settings/information from the file.

Thanks,

lucas
11-16-2006, 08:13 AM
Hi Tanner,
I think what your looking for is the dictionary object in Excel:

Dim d 'Create a variable
Set d = CreateObject(Scripting.Dictionary)
d.Add "a", "Athens" 'Add some keys and items
d.Add "b", "Belgrade"
d.Add "c", "Cairo"

TanerPro
11-16-2006, 08:31 AM
Thanks, i hope so
i tried but object required error at line Set d = CreateObject(Scripting.Dictionary)
Need a reference? And how do we get back inserted data?

Thanks again

TanerPro
11-16-2006, 08:38 AM
i think the problem is with the quotes
Set d = CreateObject("Scripting.Dictionary")
but how do we store it in excel?

lucas
11-16-2006, 08:49 AM
I searched the forum for dictionary and found this example from one of Malcolm's posts.......
Option Explicit
Sub ListCount()
Dim d As Object, a
Dim Lst As New Collection
Dim cel As Range, i As Long
Set d = CreateObject("Scripting.Dictionary")
For Each cel In Range(Cells(5, 1), Cells(5, 1).End(xlDown))
On Error Resume Next
d.Add Item:=cel.Text, key:=cel.Text
Next
a = d.Items
For i = 0 To d.Count - 1
With Sheets(2)
.Cells(i + 1, 1) = a(i)
.Cells(i + 1, 2).FormulaR1C1 = "=COUNTIF(Sheet1!C[-1],Sheet2!RC[-1])"
End With
Next i
End Sub

mdmackillop
11-16-2006, 02:16 PM
Hi Steve,
MS Help is missing the quotes around 'Scripting Dictionary', which causes the error noted.

mdmackillop
11-16-2006, 02:21 PM
Hi TanerPro,
Welcome to VBAX
Can you explain a bit what you're trying to do? Is AutoCad just an example or are you looking to link the programmes?
Regards
MD

Emily
11-16-2006, 08:50 PM
I think you can use document properties, especially Custom Property.

Please visit:
http://www.cpearson.com/excel/docprop.htm

TanerPro
11-17-2006, 12:20 AM
I think you can use document properties, especially Custom Property.



Emily, thank you very much, this is just i was looking


Hi TanerPro,
Welcome to VBAX
Can you explain a bit what you're trying to do? Is AutoCad just an example or are you looking to link the programmes?
Regards
MD

mdmackillop, That was just an example, i thought may be someone using AutoCAD may understand what i am trying to do.


I searched the forum for dictionary and found this example from one of Malcolm's posts.......




VBA:



Option Explicit Sub ListCount() Dim d As Object, a Dim Lst As New Collection Dim cel As Range, i As Long Set d = CreateObject("Scripting.Dictionary") For Each cel In Range(Cells(5, 1), Cells(5, 1).End(xlDown)) On Error Resume Next d.Add Item:=cel.Text, key:=cel.Text Next a = d.Items For i = 0 To d.Count - 1 With Sheets(2) .Cells(i + 1, 1) = a(i) .Cells(i + 1, 2).FormulaR1C1 = "=COUNTIF(Sheet1!C[-1],Sheet2!RC[-1])" End With Next i End Sub




Lucas, thanks very much.

I think dictionary object is more featured kind of Collection object, isn't it.?
If we use scripting in our software, do we have to pack runtimes in the installer or it exists in every machine?

Best regards,
Taner

lucas
11-17-2006, 08:56 AM
The reason I mentioned dictionary object is that XRecord is a function in the acad dictionary object as I understand it....
vba code for autocad:
Public Sub WriteXRec()
Dim oDict As AcadDictionary
Dim oXRec As AcadXRecord
Dim dxfCode(0 To 1) As Integer
Dim dxfData(0 To 1)
Set oDict = ThisDrawing.Dictionaries.Add("SampleTest")
Set oXRec = oDict.AddXRecord("Record1")
dxfCode(0) = 1: dxfData(0) = "First Value"
dxfCode(1) = 2: dxfData(1) = "Second Value"
oXRec.SetXRecordData dxfCode, dxfData
End Sub

Public Sub ReadXRec()
Dim oDict As AcadDictionary
Dim oXRec As AcadXRecord
Dim dxfCode, dxfData
Set oDict = ThisDrawing.Dictionaries.Item("SampleTest")
Set oXRec = oDict.Item("Record1")
oXRec.GetXRecordData dxfCode, dxfData
Debug.Print dxfData(0)
Debug.Print dxfData(1)
End Sub

If you run WriteXRec..
then run ReadXRec
you will see in the immediate window:
First Value
Second Value

lucas
11-17-2006, 09:43 AM
If we use scripting in our software, do we have to pack runtimes in the installer or it exists in every machine?

Best regards,
Taner
It should exist. No need to add it to the installation..

TanerPro
11-18-2006, 12:14 AM
The reason I mentioned dictionary object is that XRecord is a function in the acad dictionary object as I understand it....
vba code for autocad:
Public Sub WriteXRec()
Dim oDict As AcadDictionary
Dim oXRec As AcadXRecord
Dim dxfCode(0 To 1) As Integer
Dim dxfData(0 To 1)
Set oDict = ThisDrawing.Dictionaries.Add("SampleTest")
Set oXRec = oDict.AddXRecord("Record1")
dxfCode(0) = 1: dxfData(0) = "First Value"
dxfCode(1) = 2: dxfData(1) = "Second Value"
oXRec.SetXRecordData dxfCode, dxfData
End Sub

Public Sub ReadXRec()
Dim oDict As AcadDictionary
Dim oXRec As AcadXRecord
Dim dxfCode, dxfData
Set oDict = ThisDrawing.Dictionaries.Item("SampleTest")
Set oXRec = oDict.Item("Record1")
oXRec.GetXRecordData dxfCode, dxfData
Debug.Print dxfData(0)
Debug.Print dxfData(1)
End Sub

If you run WriteXRec..
then run ReadXRec
you will see in the immediate window:
First Value
Second Value

Thanks, and in addition xrecords are saved inside drawings. You will reach First and second values whenever you want in next sessions :friends: