Posts by Imbuzi

Hi Wes,

Instead of the find an replace have you might want to think about using INDIRECT and ADDRESS function to identify which spreadsheet you want to link to.

Have a look at the following link as it should shed some light on what I am suggesting.

http://www.ozgrid.com/forum/viewthread.php?tid=9332

HTH

Graeme

Happy to be of assistance

Well... It seems you were right Andy that was not the code that I needed as this does not utlise second mailbox but only the default mailbox.

Any Ideas?

TIA

Graeme

Hi Trees,

this code should do what you after.

Sub SwapFile()


Filename1 = ActiveWorkbook.Name
ActiveWindow.Caption = "File1"
'your macro does it's thing
'If your code creates a new workbook then...
Filename2 = ActiveWorkbook.Name
ActiveWindow.Caption = "File2"


'use this line of code to switch between the files
Windows("File1").Activate




'once you are done remember to cleanup
'in file1
ActiveWindow.Caption = Filename1
'in file2
ActiveWindow.Caption = Filename2


End Sub

HTH

Graeme

Hi Andy,

I have managed to find some code on KB that has done the trick...

just for reference here it is

Sub TestSentOnBehalfOfName()


   Dim myOlApp as Outlook.Application
   Dim myItem as Outlook.MailItem


   ' Create an Outlook application object
   Set myOlApp = New Outlook.Application


   ' Creates a new MailItem form
   Set myItem = myOlApp.CreateItem(olMailItem)


   ' Set the "From" field
   myItem.SentOnBehalfOfName = "Jon Grande"


   ' Display the item
   myItem.Display


End Sub

Thanks once again for the offer of help

Graeme

Hi All,

The current project I am trying to tackle is as follows.

I have two email accounts in outlook. What I need to do is specifiy which account Excel needs to use when it sends an email.

I have been looking at the help files but I seem to be getting no where fast. Does anyone know how I can specify which email account excel is to use at the moment it is just using my default email account.

TIA

Graeme

Hi All

Does anyone know if it is possible to interface with PDFEdit using VBA. The company I work ofr has recently purchased PDFEdit and know I am being asked if it is possible to automate the pdf file creation.:o

The main problem that I am having is the fact that they did not even install the Help files:o:o:o:o

TIA

Graeme

Here is the code that I have so far but it keeps on returning an error "Operation must use an updatable query

UPDATE [pasfsprob-Type Data], TranDate SET [pasfsprob-Type Data].TRANS_DT = (SELECT DISTINCT Pasfsdat.TRANS_DT
FROM Pasfsdat;)
WHERE ((([pasfsprob-Type Data].TRANS_DT) Is Null));

All,

This is what I am attempting to do:

I have Table A that has the same date stamp on all of the records and I have another Table B that I need to update with the same date stamp.

What I would like to do is create an update query that will update Table B based on a select distinct from Table A

Can anyone give me a hand with the syntax.

TIA Graeme

p.s. I do have a work around that creates a table with the distinct date record from Table A from which I can then update Table B but I don't think this is the most efficient way of doing things.

Thanks Andy...

That bit of code did exactly what I needed it to do.

Many thanks

Graeme:yes:

Hi All

I need to watch for a shell window that is opened by another spreadsheet and start a timer. Then once the shell has closed the timer ends.

I have managed to find some code that does this but it only works if the code opens the shell.

Option Explicit


Private Declare Function OpenProcess Lib "kernel32" ( _
    ByVal dwDesiredAccess As Long, _
    ByVal bInheritHandle As Long, _
    ByVal dwProcessId As Long) As Long


Private Declare Function GetExitCodeProcess Lib "kernel32" ( _
    ByVal lnghProcess As Long, _
    lpExitCode As Long) As Long


'// If your going to be working with Systems that support security
'// settings eg NT, XP the access will be checked against any
'// security descriptor for the target process, so use this Const
'// Sets all possible access flags for the process object.
Private Const PROCESS_ALL_ACCESS = &H1F0FFF


Public Function ShlProc_IsRunning(ShellReturnValue As Long) As Boolean
Dim lnghProcess As Long
Dim lExitCode As Long
Dim lRet As Long


'//Get the process handle
lnghProcess = OpenProcess(PROCESS_ALL_ACCESS, 0&, ShellReturnValue)
If lnghProcess <> 0 Then
    '// The GetExitCodeProcess function retrieves the
    '// termination status of the specified process.
    GetExitCodeProcess lnghProcess, lExitCode
    If lExitCode <> 0 Then
        '// Process still Running
        ShlProc_IsRunning = True
    Else
        '// Process completed
        ShlProc_IsRunning = False
    End If
End If


End Function


Sub ShellTester()
Dim RetVal As Long
'//
'// When you Shell out to an Application the Return Value
'// is the Applications Task ID
'// in order to determine if it has Terminated we need to check
'// if there is an existing process object
'//   &gt; OpenProcess function opens an existing process object.
'//
    Range("Start_Time") = Time
    Range("Finish_Time") = ""




On Error Resume Next
'// On WinXP Calc.exe @ C:\WINDOWS\System32\
'// On Win9x Calc.exe @ C:\WINDOWS\
RetVal = Shell("C:\WINDOWS\System32\CALC.EXE", 1)
On Error GoTo 0


If RetVal = 0 Then End
'// Ok, lets loop until the App process is terminated!
Do While ShlProc_IsRunning(RetVal) = True
    DoEvents
Loop


    Range("Finish_Time") = Time
End Sub

TIA

Graeme

Hi Cmark,

Check that on the Security|Trusted Sources that the Trust Access to Visual Basic Project is checked. This may be what is causing your problems.

HTH

Graeme

Hi Epidemic,

I have been playing around with task scheduler getting it to open an excel session. and I haven't seen the error that you mentioned. The only ideas that I came up with was this. Has your XP Pro machine been locked down by your IT Dept.

Does your scheduled task open a new session of Excel or is it just openning a workbook.

Lastly what version of Excel were you running prior to your upgrade.

Graeme

Guys,

I would agree with you both. I responded to a few posts in the the last 24hrs and all of my posts have disappeared:o

I just sent a U2U to Dave to let him know.

Thanks Weasel

I managed to get it to work just after I posted.;;)

Thanks.

Graeme

Ps hows the PeopleSoft Retrieval going?

Not to worry I managed to solve it all I needed todo was use the INDIRECT function arround the ADDRESS.

=VLOOKUP(A6,INDIRECT(ADDRESS(6,1,,1,$D$2)&":"&ADDRESS(455,60)),VALUE(D1+2),0)

:biggrin::biggrin:

I found the solution in one of the news letters.

TIA

Graeme

Hi All,

I need some help with the ADDRESS function and a VLOOKUP.

My problem is as follows I have several sheets all with data in the same cells. What I am attempting to do is to use a vlookup to find the relevant data on the relevant tab.

The issue that I have is I have been unable to get the ADDRESS function to work within the Vlookup. it works perfectly when I enter it in a seperate cell. But when Input it as the table array in the lookup it returns #value.

Is this because the Address function returns a string and not a true range reference?

The Sheet_text in the Address function is the only variable that changes

TIA

Graeme

Hi j.v.a.

you might want to have a look at the downloads that are on offer here on the Ozgrid.

http://ozgrid.com/forum/viewthread.php?tid=4458

HTH

Graeme

Weasel,

You are quite right Peoplesoft is a web based front end to a database.

There is a SQL query writing interface available in PeopleSoft which allows mere mortals to write SQL however if you are able to write SQL you would find the user interface a bit slow. the information can then be output into Excel or CSV document.

The other option is to link directly into the Db using ODBC. This will depend on the IT nazis if they give you the access that you need.

I believe that the SQL Tester Dennis has may be of use to you.

HTH

Graeme

Thanks, Guys for the help...

I managed to establish that the reason why the workbook was coming up with #Value! was due to the fact the orginal links that were used in a SumIf formula were corrupted (not sure how) anyways I managed to get the file sent to me as values instead.

Thanks

Graeme

:yes: