Solved: upload file

hi,

does excel vba have file upload function?
I've done a quick search on internet for this tutorial but found none so far.
If you know where i can find this tutorial online, pls let me know, thanks.

regards

Uh... maybe. Upload to where? Databases? Websites? You'll need to be a little more specific before someone can help you here...

sorry, upload to local pc or intranet server, not web server, thanks.

Isn't that just a case of setting the correct filepath?

i want to create a userform with a button "upload file".
When user click on the button, the upload file box will appear.
Allowing the user to choose which file to upload.
the form will create a folder if not exist, in the pre-defined folder, and store that file in that folder.
eg.
i have a folder name "uploaded file" in the server.
A user upload "apple.xls".
VBA will create a new folder at this directory:
c:/uploaded file/apple/
and store that file in that directory:
c:/uploaded file/apple/apple.xls
the whole process does not open up the apple.xls.
is there such a tutorial?

Hi Ken :hi:

So Benong, you're working in a workbook (say Control.xls), and you want to let the user browse to a file, upload it to your server, and store it in a subdirectory of your server path? Is that correct?

Out of curiosity, is the server drive mapped to a drive letter, or do you have the full \\servername\share path to it? (I'm doubting that you would try and upload the file to C:\anything, as that looks like a local drive setup...

(Hi Ted & Austen!)

Using a shared drive scenario and assuming that you wanted the subfolder name as part of the filename, insert this into a module. Change the path to the baseFolder to suit your network path.

I did not check to see if the file exists. You might want to do that with the Dir() command and use Kill() to delete it if you want it replaced.

[vba]Declare Function CopyFile Lib "kernel32" Alias "CopyFileA" (ByVal _
lpExistingFileName As String, ByVal lpNewFileName As String, ByVal _
bFailIfExists As Long) As Long


Sub CopyMyFiles()
Dim baseFolder As String, fileNames() As String
Dim newFolder As String, subFolder As String, i As Integer

baseFolder = "x:\test\" 'Use trailing backslash

'Exit if baseFolder does not exist
If Dir(baseFolder, vbDirectory) = "" Then
MsgBox baseFolder & vbLf & "does not exist.", vbCritical, "Macro Ending"
Exit Sub
End If

'Pick Files to Copy
fileNames() = GetFile("*.xls", "Select File(s)")

'Exit if Cancel was pressed. No files selected. Otherwise, get the subfolder name part.
On Error GoTo ExitSub
subFolder = fileNames(1)
On Error GoTo 0

'Copy each file selected to the new sub folder.
For i = 1 To UBound(fileNames)
'Create a folder from the base and sub folder names
subFolder = fileNames(i)
newFolder = baseFolder & GetBaseNoExt(subFolder) & "\"
If Dir(newFolder) = "" Then MkDir newFolder
CopyFile fileNames(i), newFolder & GetBaseName(fileNames(i)), False
Next i

ExitSub:
End Sub

Function GetBaseNoExt(sFilenameWithExtension As String) As String
Dim baseName As String
baseName = GetBaseName(sFilenameWithExtension)
GetBaseNoExt = Left(baseName, InStrRev(baseName, ".") - 1)
End Function

Function GetBaseName(stFullName As String) As String
Dim stPathSep As String 'Path separator character
Dim iFNLength As Integer 'Length of stFullName
Dim i As Integer
stPathSep = Application.PathSeparator
iFNLength = Len(stFullName)
'Find last path separator character, If there Is one
For i = iFNLength To 1 Step -1
If Mid(stFullName, i, 1) = stPathSep Then Exit For
Next i
GetBaseName = Right(stFullName, iFNLength - i)
End Function

Function GetFile(Optional sInitialFilename As String, Optional sTitle As String = "Select")
With Application.FileDialog(msoFileDialogOpen)
Dim vFN() As String, iFC As Integer
.InitialFileName = sInitialFilename
.Title = sTitle
.ButtonName = "&Select"
.AllowMultiSelect = True
.Filters.Add "Excel Files", "*.xls", 1
If .Show = -1 Then
ReDim vFN(1 To .SelectedItems.Count)
For iFC = 1 To .SelectedItems.Count
vFN(iFC) = .SelectedItems(iFC)
Next iFC
End If
GetFile = vFN()
End With
End Function[/vba]

Dear Kenneth,
I didn't expect the code to be so long/complex.
What seems to me a normal & simple daily task of selecting files from folders appears NOT simple at all!
I really appreciated alot. Many many thanks for your guidance.

Hi Kennath,

i need to upload 100+ files from my local to a website. this needs to be done one by one...each file uploading takes around 1-2 mins. file types are xml/zip
can we automate this using vba

Welcome to the forum! You need to start your own thread.

Explain in it, is this an FTP site, html, htm, and does it need user/password? Without having access to a similar site, it is kind of hard to help.

These things are best done by FTP when possible. There are several examples to do that.

I have not tried this one. https://stackoverflow.com/questions/...load-text-file