Save files to generalized location?

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...
>
> 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" <[email protected]> wrote in
message news:[email protected]...


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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>