CODEWITHSUNDEEP
excellaravelrestweblaravel-4
Dennis Braga
Dennis Braga

Reputation: 1478

How to upload a file from a sheet (via VBA macro) to a laravel application?

I'm trying to make a spreedsheet "upload itself" to a RESTful web service made in Laravel 4.

I have a web form who does the same work, but I need to, instead of make the user go to the web application and manually upload the file, make the sheet capable of upload itself from a click of a button (using macros). I have a method that receives the Input::file('filename') and open the file to read and stuff. I'm using the Microsoft.XMLHTTP VBA object to send the request to the WS. Saddly, I ain't seem to be able to upload the god damm file! I'm sending the path (absolute path) in the post method, but isn't working.

The problem is: How I do this within a VBA code? How do I upload a file to the server through a VBA code? And, if it is possible, how to made that compatible with a laravel application?

EDIT

To proper answer to @Andreyco's question, I'm making this edit.

That's what I receive in the VBA Debug Tool when I return the dump of Input::all()

Array
(
    [spreedsheet] => C:\Users\Android\Desktop\tarifa.xls
)

...but, when I receive the response from the web form, it looks like this.

Array
(
    [_token] => rvtkLep6rwvkvvXc3u0WoO6nyldylp9xI36n6gb2
    [spreedsheet] => Symfony\Component\HttpFoundation\File\UploadedFile Object
    (
        [test:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 
        [originalName:Symfony\Component\HttpFoundation\File\UploadedFile:private] => tarifa.xls
        [mimeType:Symfony\Component\HttpFoundation\File\UploadedFile:private] => application/vnd.ms-excel
        [size:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 43520
        [error:Symfony\Component\HttpFoundation\File\UploadedFile:private] => 0
        [pathName:SplFileInfo:private] => /tmp/phpRsX5bf
        [fileName:SplFileInfo:private] => phpRsX5bf
    )
)

... because of Laravel structure and stuff. Hope it will be useful.

Upvotes: 2

Views: 8672

Answers (3)

Jack Miller
Jack Miller

Reputation: 7667

Here a complete, working example. If you do not need the "Please Wait" dialog just use the first code snippet and delete UploadThisFileMain thereof. Also note the server PHP test script at the very end.

Sub UploadThisFileMain()
   If ActiveWorkbook.Saved = False Then
       MsgBox "This workbook contains unsaved changes. Please save first."
       Exit Sub
   End If
   Dim ret
   ret = StartProcessing("File uploading, Please Wait...", "UploadThisFile")
   If (ret = True) Then
       MsgBox "Upload successful!"
    Else
       MsgBox "Upload failed: " & ret
   End If
End Sub

Private Function UploadThisFile()
    Dim bound As String
    bound = "A0AD2346-9849-4EF0-9A93-ACFE17910734"

    Dim url  As String
    url = "https://<YourServer>/index.php?id={" & bound & "}"

    Dim path As String
    path = ThisWorkbook.path & "\" & ThisWorkbook.Name

    sMultipart = pvGetFileAsMultipart(path, bound)

    On Error Resume Next

    Dim r
    r = pvPostMultipart(url, sMultipart, bound)

    If Err.Number <> 0 Then
      UploadThisFile = Err.Description
      Err.Clear
    Else
      UploadThisFile = True
    End If
End Function

'sends multipart/form-data To the URL using WinHttprequest/XMLHTTP
'FormData - binary (VT_UI1 | VT_ARRAY) multipart form data
Private Function pvPostMultipart(url, FormData, Boundary)
  Dim http 'As New MSXML2.XMLHTTP

  'Create XMLHTTP/ServerXMLHTTP/WinHttprequest object
  'You can use any of these three objects.
  'Set http = CreateObject("WinHttp.WinHttprequest.5")
  'Set http = CreateObject("MSXML2.XMLHTTP")
  Set http = CreateObject("MSXML2.ServerXMLHTTP")

  'Open URL As POST request
  http.Open "POST", url, False

  'Set Content-Type header
  http.setRequestHeader "Content-Type", "multipart/form-data; boundary=" + Boundary

  'Send the form data To URL As POST binary request
  http.send FormData

  'Get a result of the script which has received upload
  pvPostMultipart = http.responseText
End Function

Private Function pvGetFileAsMultipart(sFileName As String, Boundary As String) As Byte()
    Dim nFile           As Integer
    Dim sPostData       As String
    '--- read file
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        sPostData = StrConv(baBuffer, vbUnicode)
    End If
    Close nFile
    '--- prepare body
    sPostData = "--" & Boundary & vbCrLf & _
        "Content-Disposition: form-data; name=""uploadfile""; filename=""" & Mid$(sFileName, InStrRev(sFileName, "\") + 1) & """" & vbCrLf & _
        "Content-Type: application/octet-stream" & vbCrLf & vbCrLf & _
        sPostData & vbCrLf & _
        "--" & Boundary & "--"
    '--- post
    pvGetFileAsMultipart = pvToByteArray(sPostData)
End Function

Private Function pvToByteArray(sText As String) As Byte()
    pvToByteArray = StrConv(sText, vbFromUnicode)
End Function

Create a new module Processing_Code:

Public Processing_Message As String
Public Macro_to_Process As String
Public Return_Value As String

Function StartProcessing(msg As String, code As String)

   Processing_Message = msg    'Set the message that is displayed
                               'in the dialog box

   Macro_to_Process = code     'Set the macro that is run after the
                               'dialog box is active

   Processing_Dialog.Show      'Show the Dialog box

   StartProcessing = Return_Value
End Function

Create a form Processing_Dialog. Set StartUpPosition to 2 - CenterScreen. Add code:

Private Sub UserForm_Initialize()

   lblMessage.Caption = Processing_Message  'Change the Label
                                            'Caption

End Sub

Private Sub UserForm_Activate()

   Me.Repaint                                        'Refresh the UserForm
   Return_Value = Application.Run(Macro_to_Process)  'Run the macro
   Unload Me                                         'Unload the UserForm

End Sub

Now add a button to your Worksheet (If there is no "Developer" tab, go to "Options" -> "Customize Ribbon" -> enable checkbox "Developer") and assign macro UploadThisFileMain.

For the server part use this PHP test script:

<?php
foreach (getallheaders() as $name => $value) {
    echo "$name: $value\n";
}

echo "POST:";
print_r($_POST);
echo "GET:";
print_r($_GET);
echo "FILES:";
print_r($_FILES);

$entityBody = file_get_contents('php://input');
        echo "Body:$entityBody";

exit;
$base_dir = dirname( __FILE__ ) . '/upload/';
if(!is_dir($base_dir))
    mkdir($base_dir, 0777);
move_uploaded_file($_FILES["uploadfile"]["tmp_name"], $base_dir . '/' . $_FILES["uploadfile"]["name"]);
?>

Sources:

Upvotes: 5

Jack Miller
Jack Miller

Reputation: 7667

Simply posting file directly as binary body:

Sub UploadThisFile()
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "LOADING..."

    Dim url  As String
    url = "https://<YourServer>/index.php"

    Dim path As String
    path = ThisWorkbook.path & "\" & ThisWorkbook.Name

    sData = pvGetFileAsData(path)
    mimeType = "application/vnd.ms-excel.sheet.macroEnabled.12"

    On Error Resume Next

    Dim r
    r = pvPost(url, mimeType, sData)

    Range("A1").Select
    If Err.Number <> 0 Then
      ActiveCell.FormulaR1C1 = "Upload failed: " & Err.Description
      Err.Clear
    Else
      ActiveCell.FormulaR1C1 = r
    End If
End Sub

Private Function pvPost(url, mimeType, body)
  Dim http 'As New MSXML2.XMLHTTP
  Set http = CreateObject("MSXML2.ServerXMLHTTP")
  http.Open "POST", url, False
  http.setRequestHeader "Content-Type", mimeType
  http.send body
  pvPost = http.responseText
End Function

Private Function pvGetFileAsData(sFileName As String) As Byte()
    Dim nFile           As Integer
    Dim sPostData       As String
    nFile = FreeFile
    Open sFileName For Binary Access Read As nFile
    If LOF(nFile) > 0 Then
        ReDim baBuffer(0 To LOF(nFile) - 1) As Byte
        Get nFile, , baBuffer
        pvGetFileAsData = baBuffer
    End If
    Close nFile
End Function

Store file on server side via PHP script:

$entityBody = file_get_contents('php://input');
file_put_contents('file.xlsm', $entityBody);

Upvotes: 0

Dennis Braga
Dennis Braga

Reputation: 1478

I just figure it out in the follow IT Blog and it served me "like a glove"! Just two simple VBA functions/methods that did the job so f**king well! I Just needed to pass the file and the URL and it was done!

Thanks @Andreyco for your help! ;)

Upvotes: 0

Related Questions