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.
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.
Code- Dim ws As Worksheet, strFile As Variant
- ThisWorkbook.Sheets("Automate").Columns("A:C").ClearContents
- Set ws = ThisWorkbook.Sheets("Automate") 'set to current worksheet name
- strFile = Application.GetOpenFilename("Text Files (*.txt),*.txt", , "Please select text file...")
- If strFile <> False Then
- With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A5"))
- .TextFileParseType = xlDelimited
- .TextFileCommaDelimiter = True
- .Refresh
- End With
- End If
-
-
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.
Try this
Code- Sub Sample()
- Dim fil As Variant
- Dim iX As Integer
- Dim FilName As String
- ''///Open File to search
- fil = Application.GetOpenFilename(MultiSelect:=True)
- If fil = False Then
- MsgBox "No file Selected"
- Exit Sub
- End If
- If IsArray(fil) Then ''/// If user selects multiple file
- For iX = LBound(fil) To UBound(fil)
- If Right(fil(iX), Len(fil(iX)) - InStrRev(fil(iX), ".")) Like "xl**" Then
- Workbooks.Open (fil(iX))
- ElseIf Right(fil(iX), Len(fil(iX)) - InStrRev(fil(iX), ".")) = "txt" Then
- CreateObject("Shell.Application").Open (fil(iX))
- End If
- Next iX
- Else ''/// If user selects single file
- If Right(fil, Len(fil) - InStrRev(fil, ".")) Like "xl**" Then
- Workbooks.Open (fil)
- ElseIf Right(fil, Len(fil) - InStrRev(fil, ".")) = "txt" Then
- CreateObject("Shell.Application").Open (fil)
- End If
- End If
- End Sub
-
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
-
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?
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.
Code3. 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.