asked on

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

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

Jacques Geday

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

RPUKsupport

ASKER

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

Jacques Geday

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

RPUKsupport

ASKER

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?

Jacques Geday

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

RPUKsupport

ASKER

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

Jacques Geday

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

RPUKsupport

ASKER

Yes please, delete STWL1 as well

Jacques Geday

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

RPUKsupport

ASKER

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

Jacques Geday

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

RPUKsupport

ASKER

No 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

Jacques Geday

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

RPUKsupport

ASKER

Thanks, but I get a new error now

Runtime error '91'

Once this comes up the export.csv file opens up with a range selected

ASKER CERTIFIED SOLUTION

Jacques Geday

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

RPUKsupport

ASKER

Perfect. that parts works great. How do you want to continue?

Jacques Geday

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

RPUKsupport

ASKER

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

Jacques Geday

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

RPUKsupport

ASKER

sorry, my bad, I saved the file wrong.
All is good, thanks, I will post the next part

RPUKsupport

ASKER

https://www.experts-exchange.com/questions/28570730/Import-data-into-sheet-change-layout-insert-date-stamp-and-create-macro-button-to-do-this.html

Jacques Geday

This question seems to be still open ?
gowflow

RPUKsupport

ASKER

the first part works great thanks