Transfer flat file to a Shared folder.

L

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
 
D

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.
 
D

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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top