Solved: General questions on excel objects...

[Papadopoulos]

Hi,

I'm trying to figure out how to approach exporting a bunch of information out of a workbook. The intent is to be able to open the file at a later date, import the information previously exported and return the file to the state it was in.
(Currently we save the whole excel file and change it's name.) One of my issues with doing this is that certain things are established via checkboxes and dropdowns. So far I haven't seen anything promising about setting the state of a checkbox or a dropdown using VBA.

Thanks for any bits of wisdom,
David

[BrianMH]

Out of curiosity why not save the whole excel file and change it's name as you currently do?

[Papadopoulos]

2.2mb per shot.
Also, the next step is to get certain parts of the data out as xml and import them to a pdf form, (This is a quoting tool)

thanks

[GTO]

I would suggest attaching a small (not a 2.2m) sample workbook to show us what steps we are wanting to accomplish.

[Papadopoulos]

Ok, let me see what I can separate without breaking too much.

[Papadopoulos]

I'm afraid it will create more questions than answers.
1, first time it asks for a user id enter best@20e, the second time, any 3 letters
2, The orange button that unhides sheets... bis is the password
3, To access the vba - r5t5pq

Now everyone will see what a mess I have created.

[mdmackillop]

Here's my approach. Create text files to hold data and control values. These can be opened and the data transferred back to the original values.

[VBA]Option Explicit

Sub Export()
Dim i As Long, j As Long
Dim cel As Range
Dim DataCells()
Dim Ctrls()
Dim ctl
Dim fs, a

ReDim DataCells(1, 5000)
ReDim Ctrls(1, 5000)

Set fs = CreateObject("Scripting.FileSystemObject")

For Each cel In ActiveSheet.UsedRange.SpecialCells(xlCellTypeConstants)
DataCells(0, i) = cel.Address
DataCells(1, i) = cel.Value
i = i + 1
Next
ReDim Preserve DataCells(1, i - 1)

Set a = fs.CreateTextFile("c:\aaa\testfile.txt", True)
For j = 0 To i - 1
a.WriteLine Ctrls(0, j) & "," & Ctrls(1, j)
Next
a.Close

On Error Resume Next
i = 0
For Each ctl In ActiveSheet.Shapes
Select Case Split(ctl.Name)(0)
Case "Drop"
Ctrls(0, i) = ctl.Name
Ctrls(1, i) = ActiveSheet.DropDowns(ctl.Name).ListIndex
Case "Check"
Ctrls(0, i) = ctl.Name
Ctrls(1, i) = ActiveSheet.CheckBoxes(ctl.Name).Value
Case Else
'continue control options
Ctrls(0, i) = ctl.Name
Ctrls(1, i) = ctl.Type

End Select
i = i + 1
Next
On Error GoTo 0

ReDim Preserve Ctrls(1, i - 1)

Set a = fs.CreateTextFile("c:\aaa\Ctrlfile.txt", True)
For j = 0 To i - 1
a.WriteLine Ctrls(0, j) & "," & Ctrls(1, j)
Next
a.Close

End Sub

[/VBA]

[Papadopoulos]

You Rock!
I'm looking forward to trying this.

Thanks,
David

[mdmackillop]

Hi David,
I've reordered and reposted my code above. There was an error in using the same variable twice to write the text files.

[Papadopoulos]

By the way, Is that a Rhodesian Ridgeback in your avatar Photo?

[mdmackillop]

It is. Sadly no longer with us.

[Papadopoulos]

Nor is mine.
Recently adopted a pair of retired Greyhounds though!