Posts by Imbuzi

Hi,

I have recieved a file from a Excel 2000 user via email and when I open the attachment in Excel XP I am prompted to update links. Even if I select don't update the file still updates the links which then turns the cells to #Value!.

I have tried saving the attachment and then openning it but it still does the same thing.

Does anyone know how I can get around this?

TIA

Graeme

Hi Weasel,

Currently where I work we use Hyperion Essbase to interface into PeopleSoft. I am not familiar with HRMS, but if you can set-up an ODBC connection you could use Access linked tables to link into Peoplesoft.

HTH

Graeme

Hi John,

I have been looking for a way to change the settings using code. I haven't found any but I did find a bit of code on John Walkenbach's site which will prompt a user to adjust their settings.

HTH

Graeme

[code]
' Make sure access to the VBProject is allowed
Dim VBP As Object ' as VBProject
If Val(Application.Version) >= 10 Then
On Error Resume Next
Set VBP = ActiveWorkbook.VBProject
If Err.Number <> 0 Then
MsgBox "Your security settings do not allow this procedure to run." _
& vbCrLf & vbCrLf & "To change your security setting:" _
& vbCrLf & vbCrLf & " 1. Select Tools - Macro - Security." & vbCrLf _
& " 2. Click the 'Trusted Sources' tab" & vbCrLf _
& " 3. Place a checkmark next to 'Trust access to Visual Basic Project.'", _
vbCritical
Exit Sub
End If
End If

[\code]

hi John,

have a look at the knowledgebase thread for some more info.

http://support.microsoft.com/d…-us;317405&Product=xl2002

Graeme

Hi John,

I have seen this problem before what you need to do is to go into Word and Excel and on the Tools|Macros|Security menu select the Trusted sources tab and check the Trust access to Visual Basic Project.

You should also set the Security Setting to Low (this is OK so long as you have Anti Virus software installed).

This should do the trick.

Graeme

Hi John

which references are you referring to? do you mean the Object library references or do you mean Application.Version?

Graeme

Isn't funny how until something is taken away you don't realise how much you depend on it....

I also had a question yesterday and shock an horror I had to wait until I got home to find the solution in a paper thing what they called again...:biggrin::biggrin:

Hi Salihyil,

It sounds like you need a unique key in order to perform your lookup. you might want to create a unique key by concatenating some of the data together on both your lookup reference and your lookup table.

e.g. A1&B1&C1 this would give you the following unique key salxxx12/1/03

HTH

Graeme

Hi Hat,

I see the Weasel beat me to the punch...

This is a possible alternative. save the code below in a new workbook. The code below will locate the workbooks you want to rename then save them with the name in B2. The workbooks will be saved in the same directory as the workbook with the code below.

'Option Explicit


'32-bit API declarations
Declare Function SHGetPathFromIDList Lib "shell32.dll" _
  Alias "SHGetPathFromIDListA" (ByVal pidl As Long, ByVal pszPath As String) As Long


Declare Function SHBrowseForFolder Lib "shell32.dll" _
Alias "SHBrowseForFolderA" (lpBrowseInfo As BROWSEINFO) As Long


Public Type BROWSEINFO
    hOwner As Long
    pidlRoot As Long
    pszDisplayName As String
    lpszTitle As String
    ulFlags As Long
    lpfn As Long
    lParam As Long
    iImage As Long
End Type






Function GetDirectory(Optional Msg) As String
    Dim bInfo As BROWSEINFO
    Dim path As String
    Dim r As Long, x As Long, pos As Integer
 
'   Root folder = Desktop
    bInfo.pidlRoot = 0&


'   Title in the dialog
    If IsMissing(Msg) Then
        bInfo.lpszTitle = "Select a folder."
    Else
        bInfo.lpszTitle = Msg
    End If
    
'   Type of directory to return
    bInfo.ulFlags = &H1


'   Display the dialog
    x = SHBrowseForFolder(bInfo)
    
'   Parse the result
    path = Space$(512)
    r = SHGetPathFromIDList(ByVal x, ByVal path)
    If r Then
        pos = InStr(path, Chr$(0))
        GetDirectory = Left(path, pos - 1)
    Else
        GetDirectory = ""
    End If
End Function


Sub BatchProcess()
    Dim FileSpec As String
    Dim i As Integer
    Dim OldWbkName As String
    Dim NewWbkName As String
    Dim strPath As String
    Application.ScreenUpdating = False
    
    'Define directory where files are to be saved
    Application.StatusBar = "Specify Directory for Saving Files..."
    strPath = ThisWorkbook.path
    filepath = GetDirectory(Msg)
    
    Application.StatusBar = ""
        
    FileSpec = "*.xls"


    'Create a FileSearch object
    Set fs = Application.FileSearch
    With fs
        .LookIn = filepath
        .Filename = FileSpec
        .Execute
        'Exit if no files are found
        If .FoundFiles.Count = 0 Then
            MsgBox "No files were found.  Check directory."
            Exit Sub
        End If
    End With


    'Loop through the files and process them
    For i = 1 To fs.FoundFiles.Count
        ChDir (filepath)
        Workbooks.Open Filename:=fs.FoundFiles(i)
        Application.StatusBar = "Processing " & i & " of " & fs.FoundFiles.Count & " files"
       ' OldWbkName = ActiveWorkbook
    Application.DisplayAlerts = False
     NewWbkName = Sheets(1).Range("B2")
     ActiveWorkbook.SaveAs Filename:=strPath & "" & NewWbkName
ActiveWorkbook.Close
    Next i
    Application.DisplayAlerts = True
    Application.StatusBar = ""


End Sub

HTH

Graeme:biggrin:

Hi b8b

You could use the filter function on the first column of data and search for "blanks" that should take care of the blank rows or insert a column next to the start of your data and use the ISBLANK formula to find the blank rows.

For the bad data you could try to use the ISTEXT function if your first column of data contains numbers and then filter the data and delete the offending rows. or if the first column of your data has a specific length you could use the LEN formula to determine the bad lines that way.

HTH

Graeme

Hi Sttrive4peace2004,

I think i'll try and get the software reloaded with all of the additional files included. Using the old boy network I should be able to get it sorted.

It still amazes me that in most companies there is always two ways of doing things. The official way that hardly ever works and then the unofficial way (Old boys network) that gets things done x10 faster.

Thanks for the tip.

:biggrin:

The LAN server has been recently migrated from a NT server to windows server 2003. as I am propbably the only person that uses access databases I am going to have to hurry up and wait a while. :o

They have also locked down the dos shell command on my user access. as they believe that this is a security risk allowing mere mortals to have permissions to do anything:(

Ps does anyone know where I can get a :gunner: from.

Hi Crystal,

I have managed to access the database by putting in on to my local drive. I have found out that the problem is not with the database but rather with the permissions that have been set on the shared drive. I still can't get my IT dept to amend the settings.:mad::mad:

Thanks for the advice.

Graeme

Hi 0ficeo34

what type of dropdown list are you using eg does it come from the forms toolbar or the control toolbox tool bar?

Graeme

Hi,

As anyone ever come across the abovementioned error message before.

We have recently migrated to XP and the Dbase that I am trying to open was created in NT it is currently sitting on a shared drive. If I move the Dbase to my local drive it works perfectly.

Does anyone know how I change the security settings to allow me to use the database on a shared drive i.e on a untrusted site?

TIA

Graeme

Hi All

I managed to find what I was looking for...
To prevent a user from amending a list in a combo box you need to amend the combo boxes Style property from StyleDropDownCombo to fmstyleStyleDropDownList.

Hopefully this will help others that have come across this issue in the past and not found the solution.

HTH

Graeme

:biggrin:

Hi Braddtrock

I am only disabling the combo box if the user has made a radio button selection first.
Graeme

Hi Derk

The combo box that I am using has been taken from the toolbox menu and is embedded on the worksheet. I would normally have used a drop down box from the forms menu. but I need to be able to disable and enable the combobox.

G

Hi,

I a have a workbook with an imbedded combobox that is linked to a named range of values. Is it possible to allow a user to be able to select a value from the list but not be able to edit or enter there own values in the combo box.

TIA

Graeme

Thanks Andy I still don't know why Mr Gates hasn't sorted this it has been bugging me since '97

Graeme