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? :confused: 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 |
Save files to generalized location?
"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 |
Save files to generalized location?
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 |
Save files to generalized location?
"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 |
Save files to generalized location?
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 |
Save files to generalized location?
"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 |
Save files to generalized location?
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 |
Save files to generalized location?
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 -- rach8 ----------------------------------------------------------------------- rach85's Profile: http://www.excelforum.com/member.php...fo&userid=1655 View this thread: http://www.excelforum.com/showthread.php?threadid=51051 |
Save files to generalized location?
"rach85" wrote in 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 |
Save files to generalized location?
"Tom Ogilvy" wrote in message ... 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 |
Save files to generalized location?
Thanks a trillion Robert & Tom! I tried both of your codes and my macro is working beautifully now :) :) -- rach85 ------------------------------------------------------------------------ rach85's Profile: http://www.excelforum.com/member.php...o&userid=16557 View this thread: http://www.excelforum.com/showthread...hreadid=510514 |
All times are GMT +1. The time now is 09:48 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com