How to get the CurDir value

[Tom]

Hi

I am programming an Access-database, which creates an Excel spreadsheet. The
program executes the following:

1) create an Excel spreadsheet
2) print a pdf file
3) send the pdf-file to a ftp-server

Now the problem is, that the pdf file will always be written in the
CurDir-folder of the Excel application. (Don't know why, but it seems that
the Excel-printout-function contains some bugs). Now the question is, how do
I get from Access the Excel-CurDir-value? Thanks in advance.

Tom

[Bob Phillips]

Tom,

The problem is that, when you are in Access, the CurDir property will return
the Access directory. You cannot qualify it with the Excel object. You could
use the DefaultFilePath property, which is where the app would normally read
and write from

xlApp.DefaultFilePath

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

"Tom" <tstr...@swissonline.ch> wrote in message
news:ODyHnOgI...@tk2msftngp13.phx.gbl...

[Tom]

I already checked this. Other question: is it possible to change the CurDir
in Excel. Can I send a ChDir with vba from Access to Excel?

Tom

"Bob Phillips" <bob.ph...@tiscali.co.uk> schrieb im Newsbeitrag
news:OfY82ggI...@TK2MSFTNGP11.phx.gbl...

[Bob Phillips]

Tom,

Wouldn't have thought so, same problem as reading it. If you want to set the
directory, you must know it, so couldn't you just use that whene saving teh
workbook, circa

Set oWB = xlapp.workbooks.Add
oWB.SaveAs "c:\Test\mybook.xls"

--
HTH

-------

Bob Phillips
... looking out across Poole Harbour to the Purbecks

"Tom" <tstr...@swissonline.ch> wrote in message

news:Owh2QlgI...@TK2MSFTNGP10.phx.gbl...

[jaf]

Hi Tom,
Yes you can.
If you have a workbook open that contains your code, you can run an Excel
macro from your Access app.
mySum = Application.Run("MYCUSTOM.XLS!My_Function", OptionalArg1,
OptionalArg2....)

A lot of people use a "personal" workbook that always opens with Excel and
contains macros usable in any workbook.
In win95 it is here...
C:\Program Files\Microsoft Office\Office\XLStart
In winnt systems it is here
C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLSTART

Copy & paste this into your personal workbook. Watch the filenames if your
using something different.

'In Excel module *****************************************************
Option Explicit
Dim MyDir As String, MyChDir As String, RetVal(1, 1), XLPath
Public Function MyFunction(MyDir As String, Optional MyChDir As String)

MyDir = CurDir
If Len(MyChDir) <= 0 Then
RetVal(0, 1) = MyDir
Exit Function
Else
ChDir MyChDir
RetVal(1, 0) = MyChDir
MyFunction = RetVal(1, 1)
End If

End Function '*******************************************************

That was the easy part.
Now add this to your project in a new Access module (see below). I did this
in word. It should run fine in Access.

'In Access module ***************************************************
Option Explicit
' Declare necessary API routines:
Declare Function FindWindow Lib "user32" Alias _
"FindWindowA" (ByVal lpClassName As String, _
ByVal lpWindowName As Long) As Long

Declare Function SendMessage Lib "user32" Alias _
"SendMessageA" (ByVal hWnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, _
ByVal lParam As Long) As Long
Dim MyCurDir As String, MyChDir As String, XLPath, MyXl As Object
Sub GetXlDir()
MyChDir = ""

GetExcel 'find excel object

MyChDir = "c:\windows" '<<<< this is the folder you want to use. Change it
to whatever.
' can be MyChDir = MyXl.Application.DefaultFilePath if that is what you want
to use.

XLPath = MyXl.Application.Run("personal.xls!MyFunction", MyCurDir) ' call xl
function to find xl's current dir
XLPath = MyCurDir
'Debug.Print "before", MyCurDir 'test output

If MyCurDir <> MyChDir Then 'if not set right, change it
XLPath = MyXl.Application.Run("personal.xls!MyFunction", MyCurDir, MyChDir)
'call excel function to change current dir.
XLPath = MyChDir 'xlpath is the variable to use
'Debug.Print "We changed it", MyCurDir, MyChDir, XLPath 'test output
'Else
'XLPath = MyXl.Application.Run("personal.xls!MyFunction", MyCurDir) 'test
output logic, xl's current dir is ok
'XLPath = MyCurDir 'test output logic, xl's current dir is ok
'Debug.Print "Its OK", MyCurDir, MyChDir, XLPath, CurDir
End If

Debug.Print XLPath
Set MyXl = Nothing

End Sub

Sub GetExcel()
'Dim MyXl As Object ' Variable to hold reference
' to Microsoft Excel.
Dim ExcelWasNotRunning As Boolean ' Flag for final release.

' Test to see if there is a copy of Microsoft Excel already running.
On Error Resume Next ' Defer error trapping.
' Getobject function called without the first argument returns a
' reference to an instance of the application. If the application isn't
' running, an error occurs.
Set MyXl = GetObject(, "Excel.Application")
If Err.Number <> 0 Then ExcelWasNotRunning = True
Err.Clear ' Clear Err object in case error occurred.

' Check for Microsoft Excel. If Microsoft Excel is running,
' enter it into the Running Object table.
DetectExcel

' Set the object variable to reference the file you want to see.
Set MyXl = GetObject("C:\Program Files\Microsoft
Office\Office\XLStart\personal.xls")

' Show Microsoft Excel through its Application property. Then
' show the actual window containing the file using the Windows
' collection of the MyXL object reference.
MyXl.Application.Visible = True
MyXl.Parent.Windows(1).Visible = True
'Do manipulations of your file here.
' ...
' If this copy of Microsoft Excel was not running when you
' started, close it using the Application property's Quit method.
' Note that when you try to quit Microsoft Excel, the
' title bar blinks and a message is displayed asking if you
' want to save any loaded files.
If ExcelWasNotRunning = True Then
MyXl.Application.Quit
End If

'Set MyXl = Nothing ' Release reference to the
' application and spreadsheet.
End Sub

Sub DetectExcel()
' Procedure dectects a running Excel and registers it.
Const WM_USER = 1024
Dim hWnd As Long
' If Excel is running this API call returns its handle.
hWnd = FindWindow("XLMAIN", 0)
If hWnd = 0 Then ' 0 means Excel not running.
Exit Sub
Else
' Excel is running so use the SendMessage API
' function to enter it in the Running Object Table.
SendMessage hWnd, WM_USER + 18, 0, 0
End If
End Sub '****************************************************************

So in your Access program...(from your other post)

Public Sub Print_to_PDF(ws As Excel.Worksheet)

Dim objDummy As Object
Dim strFileName As String

'determine filename
strFileName = Left(ws.Parent.FullName, Len(ws.Parent.FullName) - 4)

'create dummy object
Set objDummy = CreateObject("Scripting.FileSystemObject")

GetXlDir 'run the code to get/set xl's current dir.

'print pdf-file
ws.PrintOut _
Copies:=1, _
ActivePrinter:="Acrobat PDFWriter on LPT1:", _
PrintToFile:=True, _
PrToFilename:=xlpath & "\" & strFileName '<<<<<<< xlpath is
dim'd public and should work here.

'delete dummy file
objDummy.deletefile xlpath & "\" & strFileName

End Sub

--
John
johnf202 at hotmail.com

"Tom" <tstr...@swissonline.ch> wrote in message

news:Owh2QlgI...@TK2MSFTNGP10.phx.gbl...

[Tom]

Jaf,

Yes, this is the solution! Thank you.

Tom

"jaf" <m...@here.com> schrieb im Newsbeitrag
news:Oj3Yzku...@TK2MSFTNGP11.phx.gbl...

[Tom Ogilvy]

> A lot of people use a "personal" workbook that always opens with Excel and
contains macros usable in any workbook.

Don't believe it is loaded if another application loads excel through code
(OLE Automation) although it would be simple enough to open it.

Regards,
Tom Ogilvy

jaf <m...@here.com> wrote in message
news:Oj3Yzku...@TK2MSFTNGP11.phx.gbl...