Solved

help Importing data and running macros to edit data in excel 2010

Posted on 2014-11-25
23
43 Views
Last Modified: 2014-11-27
Hi
I have an extract from a third party piece of software that dumps its data after a process has ran to a csv file. I want to import this csv into a workbook with at maybe 3 sheets, one to keep all of the historical data that comes in via the csv, one to reference all of the variables that need to be in the sheet and then the main sheet that would basically tell me the results of the last process.

I know the best way to achieve this is through a macro, but the imported data has varying amounts of rows on every import, although the first 8 rows are to be deleted every time, so it would need to include variables to accommodate this.

I would like to create a macro to basically to the following:
import the data
Delete the first 8 results
import the results into the third sheet, moving older data down the sheet. The imported data comes in as rows, and i would like to switch some cells to columns (each set of results is on 8 rows, i want it to be on one row with 8 additional columns).

After that I want to set some variables, if results for A are over variable 1 then mark in red.
Then another set of variables, if results for B are within variable 2 (plus or minus variable 3)

Ultimately, i just want the user to press a button and everything is done, and the results are on screen for them and print out

It has been difficult to explain, i know what is needed and its easy to look at it on paper. Just wanted to get the data into a format thats easy reading

thanks in advance
0
Comment
Question by:RPUKsupport
  • 12
  • 11
23 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40464935
All this is fine but you are missing the cake: a sample of this csv so we can get to work !
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40465327
Here are the files, I hope it makes some sense. If not I will try and explain any areas your not sure of
Thanks
Export.csv
Results.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40466928
ok fine I can see following:

You hv a csv and a sheet called Historical Data where believe this is where you need your new data to be imported. After that it is a mystery.

My suggestion I see that you have more a project here than a question so I would suggest the following:

You decide what this question will consist of like Import Data or ... ? then we deal with it and if successful you close it and  you create a new question related to this one for the next step that you explain in short what you want to achieve and once dealt with it is closed and etc... till end of project.

Let me know your thoughts.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40467087
Hi gowflow
You are right, it is many questions in one, I'm happy to do it that way if it can all be incorporated into the same sheet.
The first thing is the easiest then. Import the data to the historical sheet and from that import, put the latest results at the top of the Results sheet, having the results in columns rather than rows, but first
Remove the following from the table,
Solution Label >>>>>> Blank
Solution Label >>>>>> Standard 1
Solution Label >>>>>> Standard 2
Solution Label >>>>>> STWL 1

And also any row where the element equals Se 196.026

Import Data Sheet    Results Sheet
solution label             Sample Reference
                                     Import date
                                     Import time
date                             Test Date
time                             Test Time
sd                                 Relate to the element columns


Is this ok for the first part or too much?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40467426
Yes clear. But not clear as to deleting whatever you want is it from the current database ? or from the imported csv ?
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40467505
The current data is there for illustration. When the import is ran, can we remove those items as they are surplus to requirement, so when the import has happened, we can remove it, unless there is a way to import only what is required, whichever is easiest
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468487
Just noticed the discrepancy with your explanation in the file where you mention to delete blank, Standard 1, Standard 2 only but above you also mentioned
Solution Label >>>>>> STWL 1

(for sure also any row that contain Se 196.026 should be deleted)

pls confirm if we should delete also all STWL 1 section as well.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40468538
Yes please, delete STWL1 as well
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468690
ok here it is.

I have installed a button on sheet Historical Data called Import CSV press on it and it will prompt you to choose a file to import and will import it in the Historical Sheet.

Check the result and let me know. Herebelow for your convenience the code.

Sub Importcsv()
Dim WS As Worksheet
Dim WB As Workbook
Dim WScsv As Worksheet
Dim MaxRowcsv As Long, I As Long
Dim sFileName As String, sPathName As String
Dim vFileName As Variant


'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Set Variables
Set WS = ActiveSheet
sPathName = ActiveWorkbook.Path

'---> Open CSV
With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = sPathName & "\*.csv"
    .Filters.Add "CSV File", "*.csv"
    .Title = "Please Enter File to Import"
    .Show
    For Each vFileName In .SelectedItems
        sFileName = vFileName
    Next vFileName
End With
If sFileName = "" Then Exit Sub

'---> Open CSV
Set WB = Workbooks.Open(sFileName)
Set WScsv = ActiveSheet
MaxRowcsv = WScsv.Range("A" & WScsv.Rows.Count).End(xlUp).Row

'---> Filter the CSV for undesireable rows
'Solution Label >>>>>> Blank
'Solution Label >>>>>> Standard 1
'Solution Label >>>>>> Standard 2
'Solution Label >>>>>> STWL 1
'element equals Se 196.026
WScsv.UsedRange.AutoFilter Field:=1, Criteria1:="blank"
WScsv.Range("A2:O" & MaxRowcsv).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WScsv.AutoFilterMode = False

WScsv.UsedRange.AutoFilter Field:=1, Criteria1:="Standard 1"
WScsv.Range("A2:O" & MaxRowcsv).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WScsv.AutoFilterMode = False

WScsv.UsedRange.AutoFilter Field:=1, Criteria1:="Standard 2"
WScsv.Range("A2:O" & MaxRowcsv).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WScsv.AutoFilterMode = False

WScsv.UsedRange.AutoFilter Field:=1, Criteria1:="STWL 1"
WScsv.Range("A2:O" & MaxRowcsv).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WScsv.AutoFilterMode = False

WScsv.UsedRange.AutoFilter Field:=3, Criteria1:="Se 196.026"
WScsv.Range("A2:O" & MaxRowcsv).SpecialCells(xlCellTypeVisible).EntireRow.Delete
WScsv.AutoFilterMode = False

MaxRowcsv = WScsv.Range("A" & WScsv.Rows.Count).End(xlUp).Row - 3

'---> Import Data at top of old data
If MaxRowcsv > 2 Then
    WScsv.Range("A3:O" & MaxRowcsv).EntireRow.Copy
    WS.Range("2:2").EntireRow.Insert
End If

'---> Close CSV
WB.Close savechanges:=False
Set WB = Nothing
Set WScsv = Nothing

'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

MsgBox "A total of " & MaxRowcsv - 2 & " rows were imported successfully to Historical Sheet"
End Sub

Open in new window


gowflow
Results-V01.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40468728
Hi, the button doesn't do anything, it just appears to be a box that is locked but if I go to macros and then run it runs ok on the initial import and deletes the data I needed deleting.
When it runs, it prompts for a location of the file, can this be set as a variable on the variables page, as it will need to run from a network share but the same place every time X:\Results\export.csv
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468826
what do you mean by 'doesn't do anything' ?? do you have Macro Enabled ??? when you open the workbook you should get a Security Warning saying some content have been disabled and a button Enable Content you should press on it to activate macros then the button will work.

Now for the variables well you have data that is scattered with blank rows and for me this shows nothing and do not see where to put this. You will need to get the variables organized until then you can leave this issue as it is and believe in the next questions I am sure will have to tackle the variables and will need to convert this into a table where then will be able to insert the ImportFileLocation as you want to be X:\Results\export.csv

Question:
Which I believe is the most important that you did not comment on
Did the macro perform correctly ?

gowflow
0
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

Author Comment

by:RPUKsupport
ID: 40468855
Hi
Capture.PNGNo warning as all security is turned off. if I click on the import csv button it doesn't do anything, it just appears to be a picture as I can type behind it.
Doesn't really matter though, as ultimately, we will just have one button on the results page correct?

So, to answer your question, the macro itself works correctly, from Developer\Macros\click the name and Run.
Uploaded book with better variables page
Results-V01.xlsm
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468903
ok its an activeX button it seems it does not work your end and is disabled due to internet security. I changed it and amended the code to include the data in Variables.

Pls chk this version.
gowflow
Results-V02.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40468915
Thanks, but I get a new error now

Runtime error '91'
error-1.PNGOnce this comes up the export.csv file opens up with  a range selected

error-2.PNG
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40468926
Sorry my mistake.
gowflow
Results-V02.xlsm
0
 

Author Comment

by:RPUKsupport
ID: 40468950
Perfect. that parts works great. How do you want to continue?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468970
it is as you like. Presume if this one done you close then put a link in here to a new question make sure the new question is a realted to this one.
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40468984
ok no problem, one thing I forgot to mention, that I have just tried, is that the export.csv file can have different totals of results in, anything upto 2000 rows. At the moment this imports up to 60 rows.

in the mean time I will write the other question out
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40468988
Sorry Sorry
What do you mean by 2000 row and now import 60 ??
Any problem with the results are they wrong ??
gowflow
0
 

Author Comment

by:RPUKsupport
ID: 40468994
sorry, my bad, I saved the file wrong.
All is good, thanks, I will post the next part
0
 

Author Comment

by:RPUKsupport
ID: 40469010
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40469128
This question seems to be still open ?
gowflow
0
 

Author Closing Comment

by:RPUKsupport
ID: 40469131
the first part works great thanks
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now