PDA

View Full Version : Solved: General questions on excel objects...



Papadopoulos
03-30-2011, 09:04 AM
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
03-30-2011, 09:06 AM
Out of curiosity why not save the whole excel file and change it's name as you currently do?

Papadopoulos
03-30-2011, 09:47 AM
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
03-30-2011, 09:57 AM
I would suggest attaching a small (not a 2.2m:104:) sample workbook to show us what steps we are wanting to accomplish.

Papadopoulos
03-30-2011, 10:48 AM
Ok, let me see what I can separate without breaking too much.

Papadopoulos
03-30-2011, 12:11 PM
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.
:doh:

mdmackillop
03-30-2011, 02:00 PM
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.

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

Papadopoulos
03-30-2011, 03:32 PM
You Rock!
I'm looking forward to trying this.

Thanks,
David

mdmackillop
03-30-2011, 03:47 PM
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
03-30-2011, 04:45 PM
By the way, Is that a Rhodesian Ridgeback in your avatar Photo?

mdmackillop
03-30-2011, 04:56 PM
It is. Sadly no longer with us.

Papadopoulos
03-30-2011, 05:38 PM
Nor is mine.
Recently adopted a pair of retired Greyhounds though!