Hi there!

Is it possible for the macro to save the files to the temp folder
(found in every pc )?

As different pc will have different directory path to the temp folder,
is there anyway to tell macro to save the files to the temp folder
without writing out the full path?

I'm doing this as these files i'm saving are going to be read in by
another external application. And the annoying part is this external
application is only able to read in files from the Temp folder.

thanks a million!!


--
rach85
------------------------------------------------------------------------
rach85's Profile: http://www.excelforum.com/member.php...o&userid=16557
View this thread: http://www.excelforum.com/showthread...hreadid=510514

"rach85" wrote in
message ...

Hi there!

Is it possible for the macro to save the files to the temp folder
(found in every pc )?

Private Declare Function GetTempPathA Lib "kernel32" (ByVal nBufferLength As
Long, ByVal lpBuffer As String) As Long

Function GetTempPath() As String
Dim lpBuffer As String
Dim nBufferLength As Long
Dim Ret As Long

nBufferLength = 255
lpBuffer = Space$(nBufferLength)
Ret = GetTempPathA(nBufferLength, lpBuffer)
GetTempPath = Left$(lpBuffer, Ret)

End Function

HTH

Robert

hi Robert,

do i place this line with the rest of the code?

Private Declare Function GetTempPathA Lib "kernel32" (ByVal
nBufferLength As
Long, ByVal lpBuffer As String) As Long


--
rach85
------------------------------------------------------------------------
rach85's Profile: http://www.excelforum.com/member.php...o&userid=16557
View this thread: http://www.excelforum.com/showthread...hreadid=510514

"rach85" wrote in
message ...

hi Robert,

do i place this line with the rest of the code?

Private Declare Function GetTempPathA Lib "kernel32" (ByVal
nBufferLength As
Long, ByVal lpBuffer As String) As Long



Hi,

Sorry, my reply was a little terse, wasn't it?

This declares a function of the Windows API. Put it at the top of your
module, after Option Explicit and before any public variable declarations.

HTH

Robert

hi Robert!

Nah, i'm pretty slow here as i know nuts about macro.

So thanks for your patience!

so the code will be placed in the module like this:

Option Explicit

Private Declare Function GetTempPathA Lib "kernel32" (ByVal
nBufferLength As
Long, ByVal lpBuffer As String) As Long

Function GetTempPath() As String
Dim lpBuffer As String
Dim nBufferLength As Long
Dim Ret As Long

nBufferLength = 255
lpBuffer = Space$(nBufferLength)
Ret = GetTempPathA(nBufferLength, lpBuffer)
GetTempPath = Left$(lpBuffer, Ret)

End Function


--
rach85
------------------------------------------------------------------------
rach85's Profile: http://www.excelforum.com/member.php...o&userid=16557
View this thread: http://www.excelforum.com/showthread...hreadid=510514

"rach85" wrote in
message ...


Yep, that's pretty much it. Then you can write a nice little function to
call it:

Option Explicit

Private Declare Function GetTempPathA Lib "kernel32" _
(ByVal nBufferLength As Long, ByVal lpBuffer As String) As Long

Function GetTempPath() As String
Dim lpBuffer As String
Dim nBufferLength As Long
Dim Ret As Long

nBufferLength = 255
lpBuffer = Space$(nBufferLength)
Ret = GetTempPathA(nBufferLength, lpBuffer)
GetTempPath = Left$(lpBuffer, Ret)
End Function

Function SaveWorkbook(Name As String, Workbook As Workbook, _
Optional MakeCopy As Boolean = False) As String

Dim strPath As String
' Get path to temp folder
strPath = GetTempPath
' Make sure we have a trailing slash..
If Not Right(strPath, 1) = Application.PathSeparator Then _
strPath = strPath & Application.PathSeparator
' Build full filename
strPath = strPath & Name

' Save workbook or a copy?
If MakeCopy Then
' Just a copy
Workbook.SaveCopyAs strPath
Else
' The workbook itself
Workbook.SaveAs strPath
End If

' Rturn path to the saved workbook...
SaveWorkbook = strPath

End Function


And call /that/ function like this (for example):

Sub Test()
MsgBox SaveWorkbook("test.xls", ThisWorkbook, True) _
& " Saved.", vbInformation
End Sub


HTH

Robert

Yes, you would do it that way or you could just use the environ function
from VBA

? environ("tmp")
C:\DOCUME~1\OgilvyTW\LOCALS~1\Temp

so
tmpPath = environ("tmp")

? environ("temp")
C:\DOCUME~1\OgilvyTW\LOCALS~1\Temp

or
tmpPath = environ("temp")

comparing the API results to the above:

? GetTempPath()
C:\DOCUME~1\OgilvyTW\LOCALS~1\Temp\

Line with "?" were entered in the immediate window for execution. The next
line shows the results of that executed line.

--
Regards,
Tom Ogilvy


"rach85" wrote in
message ...

hi Robert!

Nah, i'm pretty slow here as i know nuts about macro.

So thanks for your patience!

so the code will be placed in the module like this:

Option Explicit

Private Declare Function GetTempPathA Lib "kernel32" (ByVal
nBufferLength As
Long, ByVal lpBuffer As String) As Long

Function GetTempPath() As String
Dim lpBuffer As String
Dim nBufferLength As Long
Dim Ret As Long

nBufferLength = 255
lpBuffer = Space$(nBufferLength)
Ret = GetTempPathA(nBufferLength, lpBuffer)
GetTempPath = Left$(lpBuffer, Ret)

End Function


--
rach85
------------------------------------------------------------------------
rach85's Profile:
http://www.excelforum.com/member.php...o&userid=16557
View this thread: http://www.excelforum.com/showthread...hreadid=510514