Save files to generalized location?

[rach85]

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!!

 

[Robert ap Rhys]

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

 

[rach85]

hi Robert,

do i place this line with the rest of the code?
nBufferLength As
Long, ByVal lpBuffer As String) As Long

 

[Robert ap Rhys]

hi Robert,

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

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

 

[rach85]

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

 

[Robert ap Rhys]

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

 

[Tom Ogilvy]

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.

 

[rach85]

That works great!

But the thing is i have this userform which will save files to the tem
folder. the function will only work in the module right?

I have this:
'Save S1band'
Private Sub CommandButton1_Click()
Sheets("Sheet3").Activate
Dim fpath As String
Dim x As Long
Dim r As Range, c As Range
Set r = Sheet3.Range("A43:C" & Range("A72").End(xlUp).Row)
fpath = "*D:\Documents and Settings\Administrator\Loca
Settings\Temp\*port1.xml"
Open fpath For Output As #1
For Each c In r
If c.Column < 3 Then
If c = "" Then
Print #1, Chr(9);
Else
Print #1, c;
End If
Else
Print #1, c
End If
Next
Close #1

Unload Me
HPS1config.Show

End Sub


is there anyway for me to relate the strPath into the above userform

 

[Robert ap Rhys]

message
Make sure GetTempPath is public and the module it is sitting on is also
public. Then the appropriate line in your code should read:

fpath = GetTempPath & "port1.xml"

HTH

Robert

 

[Robert ap Rhys]

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

You're right, of course. Except that I had a bad experience back in the day
of NT4 where I had a bunch of machines some of which had a TMP environmental
variable and some of which had TEMP, but none had both. Easy to work around,
of course, but I've automatically avoided it ever since.

Robert

 

[rach85]

Thanks a trillion Robert & Tom! I tried both of your codes and my macro
is working beautifully now