Transfer flat file to a Shared folder.

[Little Penny]

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:=vbNullString,
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]

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]

Application.GetSaveAsFilename

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

Application.GetSaveAsFilename

 

[Dave Peterson]

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)