Replies

Herewith, uploaded as DelOlder.txt…

Beware that filenames containing an ampersand (&) cannot be processed and give a console error message.

JC

That was one huge amount of code for such a simple task

I’ll test it during next week.

THX – appreciate your prompt responses.

The other solution would be the BATch file which I will upload this afternoon when I get home…!

Looking forward to that – thanks a million

I’ve written it assuming UK date format – so let me know what your’s is!

Hi BATcher,

Format is dd/mm/yyyy

Currently I don’t have the FORFILES.exe available, but I’ll have a look around to see if I can get it.

…and the other solution would be?

TIA

1.) I’m not positive but I’m fairly sure that it is only the “picture” that is stoed in the form data.
2.) You can send data to a web service. So, if the Oracle db can be accessed that way you could do it. You could experiment with defining different types of data connections.

Joe

Hi Joe,

Read about the Web services after I’d done my last reply and understood from that, that any third party DB’s must be connected through web services and that MS only supports DB’s in the shape of Access or MS SQL (bit odd decision). I appreciate your confirmation of that observation

As goes for the pictures – as everything in the forms are stored in XML, I suppose the picture will be in the SVG format?
– but as far as I know Excel charts are not vector graphics?

So my concern here, is how to get the chart submitted to the database and subsequently extracted – maybe into Word, maybe into a Website and maybe into another media – without distortions.

I don’t know whether the following is representative, but for what it’s worth:
If I save a Word 2003 document with a chart copied/pasted from Excel as an HTML file, the chart is saved as a .GIF file.
If I save it from Word 2003 as a Word 2007 document (.docx), and use WinZip to look at it, the chart is stored as an .EMF file.

Most databases have a field type that can be used to store arbitrary data, usually called a [acronym=”Binary Large OBject”]BLOB[/acronym] field. You can store images, Word documents, Excel workbooks etc. in such a field.

Hi Hans,

Actually my question has two sides.

We are considering using InfoPath as a supplement to Word and I’m investigating various potential problems that I see.

1. If an Excel chart is pasted on a form how is it then stored in the database?
(given that it is not the entire workbook or document we want to store but only the chart/graphic in question)

2. From what I can find – and what I’ve been able to test so far – InfoPath only works with connections to either Access or MS SQL server – is this correct?? (We are running on an Oracle 10 db…)

Any advice or insights are highly appreciated.

TIA,

Thanks Hans,

I’ll be back on this topic in 1 weeks time / ;o)

Thanks, I’ll try to keep that in mind.

Hi Hans,

Thanks – the “scripting” references you got set – are they to the “Microsoft Scripting Runtime” (scrrun.dll)??

Dim fso As New Scripting.FileSystemObject
Dim fld As Scripting.Folder

Got one main folder, with 350+ subfolders. Each subfolder have at least the single file I’m looking for (to update) directly in it. But some have had another subfolder added, so that I need to find the file to update in the subfolder instead.

Thus, I’m trying to see, if I with the “LookIn” and “SearchFolders” (http://msdn.microsoft.com/en-us/library/aa210046(office.11).aspx) – or any other method, can find an easy way to get a tree structure to traverse.

Subsequently I’ll need to write the changes to the files in a log file. With “change done”, “file name” and “folder”.

Just needed a “mind refresh”.

Thanks.

Thanks patt. Afraid I can’t find the dcount instruction in the help for DAO recordsets…

Thus, I tried a different approach. I changed the recordset type to ‘dbOpenDynamic’ – keeping it read-only.
– then I can use .MoveLast, .MoveFirst.

Code is now:

Set rs = qy.OpenRecordset(dbOpenDynamic, 0, dbReadOnly)

If rs.EOF Then
MsgBox “Not available in the database”, vbOKOnly
Else
rs.MoveLast
i = rs.AbsolutePosition + 1
rs.MoveFirst
arrTemp = rs.GetRows(i)
End If

This fixed it. Appreciate your assistance.

Hi Rory,

Appreciate the answer, seems to work fine. However I’ve stombled on a new problem…
I’m using DAO and a ‘forward-only’ recordset, so neither .MoveLast nor .RecordCount makes much sense here.

Currently I’m doing:
arrTemp = rs.GetRows()
‘arrTemp = rs.GetRows(8)

debug.print UBound(arrTemp)

When I leave the number of rows to extract ’empty’, .GetRows return a single row only. But when I immediately after do a UBound on the array returned (the debug.print part), then I get the correct actual number (8 in this case).

I’ve read articles that suggests to do a SELECT count(*) prior to using .getrows. But having to do an extra query just to get a count, doesn’t really sound optimal, does it?

Thus, by your experience what is the easiest way to know the number of rows returned in the recordset?

Hi Hans,

Thanks – hadn’t thought of that angle.

It makes sense.

Here’s what I am trying to achieve:

1. Got a large range with variable number of rows and occasionally changing number of columns
2. Filling it into a two-dimensional ‘MasterArray’ (xRows, yCols)
3. Looping through the ‘MasterArray’ identifying a subset of records – when found added to an Array ‘Tmp1’ (yCols_T1, xRows_T1) (Here i now know the # of columns – # rows are still unknown, thus array must be transposed to have Redim Preserve)
4. Temp Array is ‘re-transposed’ to a final sub-set ‘Subset1(xRows_T1, yCols_T1)’

This snippet actually does the trick, but I’m wondering if it can’t be done in a more efficient way…?

‘Loop Sales force
For c = LBound(arrSlsInfo, 1) To UBound(arrSlsInfo, 1)
j = 1
‘Loop array of companies found for each Sales Person
For i = LBound(arrExtrMarketAll) To UBound(arrExtrMarketAll)
‘If company is covered by sales rep considered – build transposed arrTemp
If arrSlsInfo(c, 3) = arrExtrMarketAll(i, 9) Then
ReDim Preserve arrTemp(UBound(arrExtrMarketAll, 2), j)
For f = 1 To UBound(arrExtrMarketAll, 2)
arrTemp(f, j) = arrExtrMarketAll(i, f)
Next f
j = j + 1
End If
Next i

‘Re-transpose arrTemp – to get “correctly” oriented subset
ReDim arrCosCovered(LBound(arrTemp, 2) To UBound(arrTemp, 2), LBound(arrTemp, 1) To UBound(arrTemp, 1))
For d = LBound(arrTemp, 2) To UBound(arrTemp, 2)
For e = LBound(arrTemp, 1) To UBound(arrTemp, 1)
arrCosCovered(d, e) = arrTemp(e, d)
Next e
Next d
Next c

Question:
Do I need to loop the temporary array to fill ‘Subset1’ or are there other more efficient ways of achieving the above ??
What is the easiest way of doing this kind of extraction from an array into a subarray and is it somehow possible to do this without having to put the values into a worksheet as an intermediary step?

…from here I can move on filling in my worksheet etc. as required.

Thanks Rory for pointing out to me, how I have wasted time during the past years

So easy and obvious once you know. I promise – I will never forget this method!!

THX

Please disregard this as I found the solution to my problem.

Set rngName = shtKeywords.Range(“Lookup”)

sTopLeft = rngName.Address
sTopLeft = Mid(sTopLeft, 1, (InStr(1, sTopLeft, “:”, vbTextCompare)) – 1)

iRows = rngName.Rows.Count
iCols = rngName.Columns.Count

ReDim arrAnaInfo(0 To (iRows – 1), 0 To (iCols – 1))

For i = 0 To UBound(arrAnaInfo)
For j = 0 To iCols – 1
arrAnaInfo(i, j) = Range(sTopLeft).Offset(i, j).Value
Next j
Next i