Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 433
  • Last Modified:

Import spreadsheet from an attachment on an email to my production spreadsheet

gowflow, continuing on and as you suggested you could do:

When an email comes in from our Western Union representative he has a copy of the spreadsheet I originally sent him with values filled out in Col H. I need those values automatically inserted into my Col H of the WU-Staging-FBME sheet - which you have.

There needs to be checking that the value from his Col H is put into the correct row of my Col H by checking the MTCN# (Col D),  the Remitter (Col E) and maybe the Sender City and County (Col F) before the value from his Col H is inserted into my Col H.

Let me know what else you need. Thank you once again for helping me out.
0
JaseSt
Asked:
JaseSt
  • 11
  • 10
1 Solution
 
gowflowCommented:
In your related question it refers to Highlighting or marking an email after processing - part 2 for MC workbook
MC workbook is Mastercard ?? Are we not updating Visa file ???
gowlfow
0
 
JaseStAuthor Commented:
Sorry, This is for working with the Visa file where the WU-Staging-FBME sheet resides.
0
 
gowflowCommented:
ok fine.
So to summ it up,
The file that is issued from the Visa file and sent to the Western Union representative, is updated in Col H and you need these updates to be reflected in the current Visa file (for sure matching the correct records)

Correct ?
gowflow
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 
JaseStAuthor Commented:
Correct. That is step 1.

The next step (next question) will be to sum up those amounts in Col H from the specific batches and put that total in the top row for each batch in Col i - which I have indicated already kicks off a code - as imperfect the code that kicks it off is.
0
 
gowflowCommented:
ok so can we call this a new command button Import data from WU or whatever ... ?
gowflow
0
 
JaseStAuthor Commented:
Yes. I guess you can call it: 'Import WU Spreadsheet from Email'. Maybe that's too long. :)
0
 
gowflowCommented:
or Import WU Confirmed Amts
gowflow
0
 
JaseStAuthor Commented:
sure, that's better
0
 
gowflowCommented:
Question
1) Is it possible that amount in  an item to be originally say 1000 and confirmed from joseph comes say 800 and in a future batch comes the same item for the balace of 200 ? like the amount to be confirmed can come split or less than the original or more for that matter ?

gowflow
0
 
JaseStAuthor Commented:
no, Josef can only pick up the full amount or none at all. however, it does happen where the amount he actually picks up is slightly different (WU will allow a 10% difference) than the amount I submitted to him
0
 
gowflowCommented:
yes but when its diffrent still you would plugin hte amt he posted right ?
gowflow
0
 
JaseStAuthor Commented:
whatever he puts in Col H I plug into my Col H - and sometimes as you see in the screenshot from the related question before, he tells me if something is wrong with the pick up and puts that into Col H
0
 
gowflowCommented:
ok here it is
1) make a copy of ur latest Visa file and give it a new name
2) open it in module1 paste the below code

 
Function GFileName(fol As String) As String
Dim vrtSelectedItem

With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = fol
    .Title = "Please choose WU File to Import: " & fol
    .Filters.Add "Excel ", "*.xls*", 1
    .InitialView = msoFileDialogViewDetails
    .Show
    
    
    For Each vrtSelectedItem In .SelectedItems
    GFileName = vrtSelectedItem
    Next vrtSelectedItem
End With

Set vrtSelectedItem = Nothing

End Function



Sub ImportWUConfirmedAmts()
Dim WS As Worksheet
Dim WSWU As Worksheet
Dim WB As Workbook
Dim I As Long, J As Long
Dim MaxRowWU As Long
Dim Rng As Range
Dim TConfirmed As Double
Dim WUFile As String
Dim C

If MsgBox("Are you ready to Import WU Confirmed Amounts ?", vbQuestion + vbYesNo, "Import WU Confirmed Amounts") = vbYes Then
    Do
        WUFile = GFileName(gstFolderWesternUnion)
        If WUFile = "" Then
            If MsgBox("No file has been selected" & Chr(10) _
                & "[OK]     to continue and select a file." & Chr(10) _
                & "[Cancel] to Exit." & Chr(10) & Chr(10) _
                & "Please make a selection.", vbInformation + vbOKCancel, "Import WU Confirmed Amounts") = vbCancel Then
                Exit Sub
            End If
        End If
    Loop Until WUFile <> ""
    Set WS = Sheets("WU-Staging-FBME")
    Set WB = Workbooks.Open(WUFile)
    Set WSWU = ActiveSheet
    MaxRowWU = WSWU.Rows(WSWU.Rows.Count).End(xlUp).Row

    For I = 3 To MaxRowWU
        If WSWU.Cells(I, "H") <> "" Then
            WS.UsedRange.AutoFilter 4, WSWU.Cells(I, "D")
            WS.UsedRange.AutoFilter 3, Criteria1:=">=" & WSWU.Cells(I, "C"), Operator:=xlAnd, Criteria2:="<=" & WSWU.Cells(I, "C")
            WS.UsedRange.AutoFilter 2, WSWU.Cells(I, "B")
            Set Rng = WS.UsedRange.SpecialCells(xlCellTypeVisible)
            For Each Row In Rng.EntireRow
                If Not Row.Row = 1 Then
                    If WSWU.Cells(I, "A") = WS.Cells(Row.Row, "A") And WSWU.Cells(I, "E") = WS.Cells(Row.Row, "E") And WSWU.Cells(I, "F") = WS.Cells(Row.Row, "F") And WS.Cells(Row.Row, "H").Value = "" Then
                        WS.Cells(Row.Row, "H").Value = WSWU.Cells(I, "H").Value
                        TConfirmed = TConfirmed + WSWU.Cells(I, "H").Value
                        J = J + 1
                        Exit For
                    End If
                End If
            Next Row
        End If
    Next I
    
WS.ShowAllData
WS.AutoFilterMode = False
MsgBox ("Confirmed Amounts Updated successfully for " & J & " records totalling " & TConfirmed)

'Close and Exit
WB.Close savechanges:=False
Set WB = Nothing
Set WSWU = Nothing
Set WS = Nothing

End If
End Sub

Open in new window


3) SAVE the workbook
4) goto your excel sheet and select sheet Main
5) make sure in the developper menu Design is clicked. Click on any yellow button and select copy and paste it near to it to the right. like the attached image

 blue button
6) right click on the button created and choose property change the background color to blue and in the caption type Import WU Confirmed Amounts
7) Doubleclick on the blue button and the code should be as follows
---------------
Private Sub CommandButton6_Click()
ImportWUConfirmedAmts
End Sub
-----------------
if it is not commandbutton6 it does not matter what matter is the sub ImportWUConfirmedAmts to be typed there.
8)Save and exit the workbook.


How it function
Basically pretty simple in the red button on main you have already set the folder location of where you want to create you WU files produced to sent to Joseph, well the files received from him sould be in the same directory, if not its not a problem once you run the sub it will point to this location and you will choose the file to import if it is elswhere then you will locate the file where it is and it will still work.

Give it a try and let me know.
gowflow
0
 
JaseStAuthor Commented:
gowflow goes for it again!!!

Thank you. I will try it out tomorrow.
0
 
gowflowCommented:
ok let me know.
gowflow
0
 
JaseStAuthor Commented:
While I don't normally save the spreadsheet attachment that Josef sends me, I guess I can do that and doing so it seemed to run perfectly with your code.

I'll close this question, however, will need to run it more over the days to come when he sends me other sheets I can test the code against to see if there are any glitches. But for now, perfecto!! Thank you, gowflow. A wizard you are.

Next part coming up.
0
 
gowflowCommented:
your welcome and glad we nailed it ... pretty fast or I should say at a logical normal speed !!! :))
ok waiting for your next one
gowflow
0
 
JaseStAuthor Commented:
Next up is this:

A button on the WU-Staging-FBME sheet so that when clicked does this:

1. Sums the individual batch amounts in Col H and inserts that total in Col i of the top row of that batch only for those where Col H has a value for each record in the batch. (A "batch" is the batch of records brought in from a single email. See attached.) a batch
If any row in a single batch has an empty value in Col H that means it has yet to be picked up by our receiver and therefore the batch can receive no further processing until all rows in the batch have SOME value.

2. When Col i is filled in it then should kick off the code that is already there, however - and here is a tricky part - I need this formula to instead of putting 1.4 in Col U to look up and input the current USD to Euro conversion rate. Is that possible?

The code that kicks off calculations and input for Cols T - Z is located in Sheet2 (WU-Staging-FBME). It can be removed and replaced with your code if you wish, of course.

Furthermore, the button should only effect those batches where Col i is filled in AND where Cols T-Z are NOT filled in already.

Thank you.

Let me know if any questions and I will then submit as a question.
0
 
gowflowCommented:
ok post it and if I hv questions I will ask
gowflow
0
 
JaseStAuthor Commented:
gowflow, also have another urgent one, if you could, as an edit to one you worked on earlier:

http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27613437.html

Sorry, but this one is a big, time consuming problem that needs solving.
0

Featured Post

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

  • 11
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now