Replies

Niven

One way around this might be to have a separate temporary import table where all fields are defined as Text 255, Required=No, Indexed=No. This table would have no indexes, and would be cleared down prior to each import.

You would then be able to check this table for possible data errors after running the append query and before moving the data to the main table. This should avoid most errors.

Nick

You are missing a second End If (it’s clearer with indenting):

If ID_Name.Value = True Then
BUSINESS_UNIT.Enabled = False
Else
If BUSINESS_UNIT.Value = True Then
ID_Name.Enabled = False
End If
End If

Nick

Hans

That is exactly what I was looking for to use in another worksheet. Your knowledge is, as usual, spot on.

Thanks

Nick

Steve

I would just offer a word of caution on this – the formula works fine so long as the day of the lease start date is not near the end of the month. If it is on say the 30th or 31st of the month you may get unexpected results.

For example using the formula =DATE(YEAR(A1),MONTH(A1)+B1,DAY(A1)) where A1 is Jan 30th, and B1 is 1 gives the result of March 2nd. This may not be what you wanted.

I’m not sure of the best way to get round this if you need to.

Nick

Pat

I have recently had to do something similar with Excel charts. I set up an array which is populated according to how many colours(intSeriesCount) are needed to be shown

ReDim agintSeriesColour(intSeriesCount)
Select Case intSeriesCount
Case 1
agintSeriesColour(0) = 17 ‘Bright green
Case 2
agintSeriesColour(0) = 17 ‘Bright green
agintSeriesColour(1) = 18 ‘Yellow
Case 3
agintSeriesColour(0) = 19 ‘Dark green
agintSeriesColour(1) = 17 ‘Bright green
agintSeriesColour(2) = 18 ‘Yellow
Case 4
agintSeriesColour(0) = 19 ‘Dark green
agintSeriesColour(1) = 17 ‘Bright green
agintSeriesColour(2) = 18 ‘Yellow
agintSeriesColour(3) = 20 ‘Light Orange
…
End Select

The chart has its colours set as follows :

For intCount = 1 To XLcht.SeriesCollection.Count
XLcht.SeriesCollection(intCount).Interior.ColorIndex = agintSeriesColour(intCount – 1)
Next

You can set the min/max values of axes as follows :

.Axes(xlValue, xlPrimary).MaximumScale = sglMaxLost
.Axes(xlValue, xlPrimary).MinimumScale = 0

HTH

Nick

Hans

Thanks for that – I will check up with my colleague on that.

Nick

Hans

As always you are completely spot on – this fixed the problem in an instant.

The reason I was using code was because I wanted to set the background colour to one that was not on the palette (a shade of light blue). I don’t know of any other way of doing this except in code.

Thanks again

Nick

Kobus

In Excel it depends what type of toolbars you have. Shortcut toolbars can ONLY be created programmatically (I think), unlike in Access.

My suggestion would be to copy the workbooks containing the toolbars & macros elsewhere (on your network or a USB pen drive) and then copy them back when your PC has been reformatted.

Nick

Graeme

Without knowing more details of your setup, it’s difficult to say exactly which would be the best way forward. If the users are getting into SQL Server via your userID, then you could create the table to store the data (including the column UserID), create a view on that table, and then create a sproc with a parameter of UserID to select from the view.

Nick

Graeme

A suggestion – depending on the amount of data you have for each user, you could create a permanent SQL Server table to store all the user data with a column UserID. You could then have a view on this table, and a sproc to call it with a parameter of UserID, so that each user would only see their own data.

HTH

Nick

You could use some VBA code like this (not complete) to create the CSV file where rng is the range of cells to be written out:

strDelimiter = “,”

‘Create a file system object and use it to create a new
‘text file.
Set fso = New FileSystemObject
Set txs = fso.CreateTextFile(strFileName, True)

‘This loop is for looping from the top of the sheet to the bottom
For lngRow = 1 To rng.Rows.Count
For lngCol = 1 To rng.Columns.Count
txs.Write Chr(34) & rng.Cells(lngRow, lngCol) & Chr(34) & strDelimiter
Next lngCol
txs.Write vbCr
Next lngRow

txs.Close

Depending on the location you could have some code to change strDelimiter accordingly.

Nick

My fault – I was looking in the wrong place in the code.

I had coded : InStr(1, rngFound.Formula, strSearchText) & it should have been : InStr(1, rngFound.Formula, strSearchText, vbTextCompare)

It now works properly.

Thanks for your help

Nick

Hans

This is the main part of the code :

For Each sht In wkb.Worksheets
Application.StatusBar = “Processing Workbook : ” & wkb.Name & ” Worksheet : ” & sht.Name
Set rngLastCell = LastCell(sht)
If Not (rngLastCell Is Nothing) Then
With sht
‘
‘———————————————————–
‘Find all references to search text
‘THIS IS WHERE MATCH CASE DOES NOT SEEM TO WORK
‘———————————————————–
Set rngSearch = .Range(.Cells(1, 1), .Cells(rngLastCell.Row, rngLastCell.Column))
If blnSearchValues Then
Set rngFound = rngSearch.Find(what:=strSearchText, LookIn:=xlValues, MatchCase:=blnMatchCase)
Else
Set rngFound = rngSearch.Find(what:=strSearchText, LookIn:=xlFormulas, MatchCase:=blnMatchCase)
End If
If rngFound Is Nothing Then
‘do nothing
Else
‘Code to store references deleted here
End If
End If
End With
End If
Next

Hans

The search code is usually in a separate workbook but I have added the module to the samplefind.xls. The “GBP” to find is in column W.

Thanks

Nick

I’ve just found out why I should have been using functions – it is because they do not show up when the user clicks Tools/Macros/Macros.. whereas the subs do, and that is not desirable for us.