PDA

View Full Version : Solved: upload file



benong
08-23-2010, 09:00 PM
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
08-23-2010, 10:23 PM
Uh... maybe. Upload to where? Databases? Websites? You'll need to be a little more specific before someone can help you here...

benong
08-23-2010, 11:54 PM
sorry, upload to local pc or intranet server, not web server, thanks.

Aussiebear
08-24-2010, 02:00 AM
Isn't that just a case of setting the correct filepath?

benong
08-24-2010, 02:38 AM
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
08-24-2010, 07:28 AM
Hi Ken :hi:

Ken Puls
08-24-2010, 08:10 AM
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 (file://\\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
08-24-2010, 09:35 AM
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.

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

benong
08-24-2010, 06:28 PM
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
08-22-2017, 04:48 AM
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
08-22-2017, 04:43 PM
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/29107404/vba-upload-text-file