Posts by CapG

    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.

    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.

    Try this


    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

    Files

    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?

    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?

    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
    3. 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)