Solved: upload file

[benong]

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

[Ken Puls]

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

[benong]

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

[Aussiebear]

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

[benong]

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?

[austenr]

Hi Ken

[Ken Puls]

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!)

[Kenneth Hobs]

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]

[benong]

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.

[Thomast]

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

[Kenneth Hobs]

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