Transfer flat file to a Shared folder.
 

The script below saves my spreadsheet to a delimited flat file. After
the file is saved is it possible to have the scripted automatically
transfer the file to a shared folder on another computer.


Sub FlatExport()
Dim FileName As Variant
Dim Sep As String
FileName =
Application.GetSaveAsFilename(InitialFileName:=vbN ullString,
FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.",
Type:=2)
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub

Thanks

Happy New Year


Little Penny

Transfer flat file to a Shared folder.
 

Do you connect to that shared folder on that other pc?

If yes, then you could look at FileCopy and Erase in VBA's help to see how to
copy a file and delete a file.

Or you could look at the Name statement that will move it across drives/folders
in one step.

But heck, if you're connected to that shared folder, why not just choose a file
on that drive when you see the .getsaveasfilename dialog?

===
If you're not connected to that folder, maybe you can save it to a common
network share that both you and the other user have access to.



Little Penny wrote:

The script below saves my spreadsheet to a delimited flat file. After
the file is saved is it possible to have the scripted automatically
transfer the file to a shared folder on another computer.

Sub FlatExport()
Dim FileName As Variant
Dim Sep As String
FileName =
Application.GetSaveAsFilename(InitialFileName:=vbN ullString,
FileFilter:="Text Files (*.txt),*.txt")
If FileName = False Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Sep = Application.InputBox("Enter a separator character.",
Type:=2)
If Sep = vbNullString Then
''''''''''''''''''''''''''
' user cancelled, get out
''''''''''''''''''''''''''
Exit Sub
End If
Debug.Print "FileName: " & FileName, "Separator: " & Sep
ExportToTextFile FName:=CStr(FileName), Sep:=CStr(Sep), _
SelectionOnly:=False, AppendData:=True
End Sub

Thanks

Happy New Year

Little Penny

--

Dave Peterson

Transfer flat file to a Shared folder.
 

On Tue, 01 Jan 2008 19:48:56 -0600, Dave Peterson
wrote:

Application.GetSaveAsFilename


How do I get the saveas dialog box to open to a folder I choosed?

Application.GetSaveAsFilename

Transfer flat file to a Shared folder.
 

First, do you mean open the file?

Dim myFilename as Variant
dim wkbk as workbook
myfilename = application.getsaveasfilename(....)
if myfilename = false then
'user hit cancel
exit sub '???
end if
set wkbk = workbooks.open(filename:=myfilename)

If you really were asking how to choose a folder -- not open a file:

Jim Rech has a BrowseForFolder routine at:
http://www.oaltd.co.uk/MVP/Default.htm
(look for BrowseForFolder)

John Walkenbach has one at:
http://j-walk.com/ss/excel/tips/tip29.htm

If you and all your users are running xl2002+, take a look at VBA's help for:
application.filedialog(msoFileDialogFolderPicker)




Little Penny wrote:

On Tue, 01 Jan 2008 19:48:56 -0600, Dave Peterson
wrote:

Application.GetSaveAsFilename

How do I get the saveas dialog box to open to a folder I choosed?

Application.GetSaveAsFilename

--

Dave Peterson