Posts by CapG

I am required to use vba to automate it on a daily basis. After importing the data, max value from 3rd column is used to update into another summary report. Hence vba is used.

I have written the code for importing the data. But I dont know how to fetch the same data when it comes as an email attachment.

No, Windows

Its 2011 and here the shared excel file is only used for automating purpose, nobody uses it modify the the data but to copy and paste into other workbooks.

It could be possible sending as an attachment but it would be in .txt file format.

So you mean the data must be exported to a text file manually?

I have never worked with this kind of programming before so here goes my problem statement ,Every hour I will receive emails from a particular sender with a constant subject for which I have created a folder under inbox called "hourly report". I need to import the body of its email received at around12.02 AM daily to my shared excel workbook(.xlsm). The body of the email is in comma seperated format as shown below.

PLACED ORDERS:

=============

DATE, HOUR, ORDERCOUNT, ORDERTOTAL

2020-12-10, 0, 0, 0

2020-12-09, 23, 18, 13

2020-12-09, 22, 29, 18

2020-12-09, 21, 25, 1

2020-12-09, 20, 29, 179

2020-12-09, 19, 25, 189

2020-12-09, 18, 30, 2

2020-12-09, 17, 35, 329

2020-12-09, 16, 22, 1

2020-12-09, 15, 35, 23

2020-12-09, 14, 37, 222

2020-12-09, 13, 29, 22

2020-12-09, 12, 32, 26

2020-12-09, 11, 45, 32

2020-12-09, 10, 25, 21

2020-12-09, 9, 26, 12

2020-12-09, 8, 12, 76

2020-12-09, 7, 14, 83

2020-12-09, 6, 10, 76

2020-12-09, 5, 3, 11

2020-12-09, 4, 3, 17

2020-12-09, 3, 9, 87

2020-12-09, 2, 5, 42

2020-12-09, 1, 9, 57

UPDATED CARTS:

=============

DATE, HOUR, CARTCOUNT, CARTTOTAL

2020-12-10, 0, 2, 2

2020-12-09, 23, 21, 1490

2020-12-09, 22, 8, 164

2020-12-09, 21, 29, 102

2020-12-09, 20, 24, 94

2020-12-09, 19, 20, 828

2020-12-09, 18, 23, 8898

2020-12-09, 16, 24, 9448

2020-12-09, 15, 90, 1249

2020-12-09, 14, 400, 14

2020-12-09, 13, 37, 88

2020-12-09, 12, 208, 838

2020-12-09, 11, 22, 9

2020-12-09, 10, 230, 8

2020-12-09, 9, 30, 742

2020-12-09, 8, 29, 86

2020-12-09, 7, 166, 753

2020-12-09, 6, 11, 4370

2020-12-09, 5, 51, 1791.4

2020-12-09, 4, 47, 173

2020-12-09, 3, 54, 3137

2020-12-09, 2, 69, 3118.65

2020-12-09, 1, 03, 47

PRODUCTS COUNT:

==============

4

Sorry the error message in the screenshot was from the first code.

Quote from royUK

Try this

Code

1. Sub Sample()
2. Dim fil As Variant
3. Dim iX As Integer
4. Dim FilName As String
7. ''///Open File to search
8. fil = Application.GetOpenFilename(MultiSelect:=True)
11. If fil = False Then
12. MsgBox "No file Selected"
13. Exit Sub
14. End If
17. If IsArray(fil) Then ''/// If user selects multiple file
18. For iX = LBound(fil) To UBound(fil)
21. If Right(fil(iX), Len(fil(iX)) - InStrRev(fil(iX), ".")) Like "xl**" Then
22. Workbooks.Open (fil(iX))
23. ElseIf Right(fil(iX), Len(fil(iX)) - InStrRev(fil(iX), ".")) = "txt" Then
24. CreateObject("Shell.Application").Open (fil(iX))
25. End If
26. Next iX
27. Else ''/// If user selects single file
28. If Right(fil, Len(fil) - InStrRev(fil, ".")) Like "xl**" Then
29. Workbooks.Open (fil)
30. ElseIf Right(fil, Len(fil) - InStrRev(fil, ".")) = "txt" Then
31. CreateObject("Shell.Application").Open (fil)
32. End If
33. End If
34. End Sub

Display More

I have twwo files that I need to open at the same time one is .xlsx and .txt. Is it possible to open both at the same time using getopenfilename() method?

After opening I need to copy all contents of both files to thisworkbook.sheets(1)

Thankyou for taking out time to write this huge code which I can hardly figure out since I'm a newbie to vba but works just fine for my requirement.

Thanks again☺

I have chunks of data in notepad(.txt) which is seperated by comma but the catch here is it doesnt start from the very first line and every chunk of data must be read to import only second half of each line to corresponding header in the excel sheet.

FOR example: This is the type of data in notepad having date,time. So only time must be extracted and copied to cell (excel file) which corresponds to specific date(16/11) and header(RunbookBCompletion) present in the notepad.

Date,RunbookBCompletionTime

20201116,05:44 AM

20201117,05:47 AM

20201118,05:39 AM

20201119,06:10 AM

20201120,05:49 AM

20201121,07:13 AM

20201122,06:01 AM

Like wise all the data must read and copy in the same manner. This report is ran once in every week so the excel should update the all the values from previous week through data in notepad

Thank you for your help!

Using legacy share option. We have a shared network in which the file resides

Quote from CapG

I have a macro enabled workbook that is generated using a custom written macro which works fine in an unshared environment, but the moment it is shared and tried to open again it gives a recovery message as We found a problem with some content in xlsm do you want us to recover as much as we can if you trust this source of workbook click yes.

As per my observation, that workbook contains 3 major tabs(sheets), In that the third sheet is the one that is causing the error.

How should I deal with this issue?

The error message is in xml that looks something like this

<?xml version="1.0" encoding="UTF-8" standalone="yes"?>

<recoveryLog xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><logFileName>error437920_02.xml</logFileName><summary>Errors were detected in file 'C:\Users\rar\dev\Lifecycle\Shopping List Split File\Shopping list\Shopping List09-09-2020.xlsm'</summary><removedRecords><removedRecord>Removed Records: Named range from /xl/workbook.xml part (Workbook)</removedRecord></removedRecords></recoveryLog>

I have a macro enabled workbook that is generated using a custom written macro which works fine in an unshared environment, but the moment it is shared and tried to open again it gives a recovery message as We found a problem with some content in xlsm do you want us to recover as much as we can if you trust this source of workbook click yes.

As per my observation, that workbook contains 3 major tabs(sheets), In that the third sheet is the one that is causing the error.

How should I deal with this issue?

Yes I will do that!

Thank you for the help royUK!

Color coding is done after applying each filter which is also done through macro.

Now I have code for two filter criteria, how do I apply filter for the third . Is it possible through macro?

Quote from CapG

Yes the dates are sample test data as I haven't got real time data and to the single column I need to apply all three criteria.

As I have first two criteria coded below ,could you please tell me how to apply criteria 3.

Code

1. Sheet1.Range("A1").CurrentRegion.AutoFilter Field:=1, Criteria1:= _
2. ">=01/01/" & Year(Date), Operator:=xlOr, Criteria2:=Application.WorksheetFunction.EoMonth(Date, 0) 'criteria 1
4. Sheet1.Range("A1").CurrentRegion.AutoFilter Field:=1,Operator:=xlFilterDynamic, Criteria1:=xlFilterNextMonth 'criteria 2

3. Filter the Deliver date column to the rest of upcoming months (exclude next month)

Sep-till the end(which could include 2021)

Actually thrice! I will have to filter same column thrice (3 criteria)

The dates in column A refers to the product specifications that could be delivered by those dates, so here we are accounting the status of each product details.After applying each criteria,it is color coded to differentiate.