Excel Graphs - Import Data

Hi Experts

I had a previous question which gowflow helped me with greatly to create tables & associated graphs for some survey information.  Further to this, you will see in the attached file that 2 buttons exist.  i would like a third button 'Import' which requests the name of the import file, and /or enables you to browse for it.  When the file is imported, Columns A - I are automatically deleted (they contain non-relevant information).

This will then allow me to proceed with the other command buttons as necessary.
Survey-Monkey-Trial-V4-1.xls
LVL 7
andymacfAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

drfrank319Commented:
Would you please post a sample input file (that would be imported to sheet "Import")?
0
andymacfAuthor Commented:
Sample file attached as requested.
Sheet-1.xls
0
gowflowCommented:
Is this what your looking for ?

Activate macros and try it. I would suggest for trial to rename your present import sheet to import old as the macro has a provision to create the sheet Import if it does not find it. Check if all your data is imported correctly.

gowflow

<<Code snippets added by byundt--Microsoft Excel Topic Advisor. This is part of an effort to improve thread quality and Google page rank by eliminating "blind links".>>
'Goes on worksheet code pane
Private Sub CommandButton3_Click()
If MsgBox("WARNING !!!! " & Chr(10) & Chr(10) _
    & "This process will import the csv file selected into sheet 'Import' and will replace the existing data." & Chr(10) & Chr(10) _
    & "Are you ready to proceed ?", vbCritical + vbYesNo, "Import Data") = vbYes Then
    ImportData
End If

End Sub

Open in new window


'Goes in regular module sheet
Sub ImportData()
Dim WS As Worksheet
Dim WScsv As Worksheet

Dim WB As Workbook
Dim I As Long, J As Long
Dim MaxRowImport As Long, MaxRowcsv As Long
Dim Rng As Range
Dim TConfirmed As Double, TColG As Double, TColH As Double
Dim WUFile As String, WUDate As String
Dim C


Do
    WUFile = GFileName(ActiveWorkbook.Path, "Please choose Data File to Import: ")
    If WUFile = "" Then
        If MsgBox("No file has been selected" & Chr(10) _
            & "[OK]     to continue and select a file." & Chr(10) _
            & "[Cancel] to Exit." & Chr(10) & Chr(10) _
            & "Please make a selection.", vbInformation + vbOKCancel, "Import Data") = vbCancel Then
            Exit Sub
        End If
    End If
Loop Until WUFile <> ""

On Error Resume Next
Set WS = Sheets("Import")

If Err <> 0 Then
    MsgBox ("Sheet 'Import' does not exist and will be created.")
    ActiveWorkbook.Worksheets.Add after:=Sheets("Main")
    Set WS = ActiveSheet
    WS.Name = "Import"
    On Error GoTo 0
End If

Set WB = Workbooks.Open(WUFile)
Set WScsv = ActiveSheet

MaxRowcsv = WScsv.UsedRange.Rows.Count

WScsv.Range("A:I").EntireColumn.Delete
WScsv.UsedRange.EntireColumn.Copy
WS.Range("A1").PasteSpecial xlPasteAll

Application.DisplayAlerts = False
WB.Close savechanges:=False
Set WS = Nothing
Set WScsv = Nothing
Set WB = Nothing
Application.DisplayAlerts = True

MsgBox ("Import completed successfully.")

End Sub


Function GFileName(Fol As String, Title As String) As String
Dim vrtSelectedItem

With Application.FileDialog(msoFileDialogFilePicker)
    .InitialFileName = Fol
    .Title = Title & Fol
    .Filters.Add "Excel ", "*.xls*", 1
    .InitialView = msoFileDialogViewDetails
    .Show
    
    
    For Each vrtSelectedItem In .SelectedItems
        GFileName = vrtSelectedItem
    Next vrtSelectedItem
    
End With

Set vrtSelectedItem = Nothing

End Function

Open in new window

Survey-Monkey-Trial-V4-1.xls
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

andymacfAuthor Commented:
Hi gowflow

I'm not sure if it is me but it seems I need to run 'Create Output' twice in order to get the data to populate properly.  Can you check this please?

Andy
0
gowflowCommented:
did you fiddle around with the code ?
gowflow
0
gowflowCommented:
the object of this question is
import data not create output but Ill hv a look at it
gowflow
0
andymacfAuthor Commented:
No, all I did was rename the 'Import' sheet as instructed
0
gowflowCommented:
ok sorry my mistake your right there was a bug in the Create output right from the past few questions you pointed out now it is fixed in this one.
Sorry again
gowflow

<<Code snippet added by byundt--Microsoft Excel Topic Advisor. This is part of an effort to improve thread quality and Google page rank by eliminating "blind links".>>
Sub CreateOutput()
Dim WSImport As Worksheet
Dim WSOutput As Worksheet
Dim MaxRowImp As Long, MaxColImp As Long, MaxRowOut As Long, I As Long, J As Long
Dim sTopic As String, sQuestion As String, sMeasure As String, sMeasureOut As String
Dim QuestMeas

Set WSImport = Sheets("Import")

On Error Resume Next
Set WSOutput = Sheets("Output")
If Err = 0 Then
    '---> Sheet Output Exist Clear data
    WSOutput.UsedRange.ClearComments
    WSOutput.UsedRange.ClearContents
    WSOutput.UsedRange.ClearFormats
    WSOutput.UsedRange.ClearNotes
    WSOutput.UsedRange.ClearOutline
Else
    '---> Sheet Output does not exist need to be created
    ActiveWorkbook.Worksheets.Add after:=Sheets(WSImport.Name)
    Set WSOutput = ActiveSheet
    WSOutput.Name = "Output"
    MaxRowOut = 1
    On Error GoTo 0
End If

'---> Create Total in sheet Import
MaxRowImp = WSImport.UsedRange.Rows.Count
MaxColImp = WSImport.Range("2:2").End(xlToRight).Column

If Left(WSImport.Range("A" & MaxRowImp).Formula, 1) = "=" Then
    '---> Re-Create the formula in this Row as it already exist
    '     to ensure correctly ploted.
    WSImport.Range(WSImport.Cells(MaxRowImp, 1), WSImport.Cells(MaxRowImp, MaxColImp)).End(xlToRight).Formula = "=COUNT(A3:A" & MaxRowImp & ")"
Else
    '---> Create the formula at MaxRowImp +1
    WSImport.Range(WSImport.Cells(MaxRowImp + 1, 1), WSImport.Cells(MaxRowImp + 1, MaxColImp)).Formula = "=COUNT(A3:A" & MaxRowImp & ")"
    MaxRowImp = MaxRowImp + 1
End If

'---> Start Process
For I = 1 To MaxColImp - 1 Step 4
    If WSImport.Cells(1, I) <> sTopic Then
        sTopic = WSImport.Cells(1, I)
    End If
    
    '---> Affect Question and Measure
    For J = 0 To 3
        QuestMeas = WSImport.Cells(2, I + J)
        sQuestion = Mid(QuestMeas, 1, InStrRev(QuestMeas, " - "))
        If sMeasure <> "" Then sMeasure = sMeasure & "|"
        sMeasure = sMeasure & Mid(QuestMeas, InStrRev(QuestMeas, " - ") + 3)
    Next J

    '---> Update Output with data read
    
    '---> Print Header if diffrent from previous
    If sMeasure <> sMeasureOut Then
        QuestMeas = Split(sMeasure, "|")
        For J = 0 To 3
            WSOutput.Cells(MaxRowOut + 1, J + 3) = QuestMeas(J)
        Next J
        sMeasureOut = sMeasure
        MaxRowOut = MaxRowOut + 2
    End If
    
    '---> Print Topic,Question,values
    WSOutput.Cells(MaxRowOut, "A") = sTopic
    WSOutput.Cells(MaxRowOut, "B") = sQuestion
    WSOutput.Cells(MaxRowOut, "C") = Val(WSImport.Cells(MaxRowImp, I))
    WSOutput.Cells(MaxRowOut, "D") = Val(WSImport.Cells(MaxRowImp, I + 1))
    WSOutput.Cells(MaxRowOut, "E") = Val(WSImport.Cells(MaxRowImp, I + 2))
    WSOutput.Cells(MaxRowOut, "F") = Val(WSImport.Cells(MaxRowImp, I + 3))
    
    '---> Increment Row in Output
    MaxRowOut = MaxRowOut + 1
    
    '---> Initialise Variables
    sTopic = ""
    sQuestion = ""
    sMeasure = ""
    QuestMeas = vbEmpty
Next I

'---> Format Output
WSOutput.Range("A:F").EntireColumn.AutoFit
WSOutput.Range("C:F").HorizontalAlignment = xlHAlignCenter

'---> Final msg
MsgBox ("Import of data to Output sheet completed successfully for " & MaxRowOut - 2 & " questions.")

End Sub

Open in new window

Survey-Monkey-Trial-V4-2.xls
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
gowflowCommented:
Did you have a chance to try out the last version ?
gowflow
0
andymacfAuthor Commented:
Hi gowflow

Just arrived at work, will be checking it very soon.

Cheers
Andy
0
gowflowCommented:
no problem take your time.
gowflow
0
andymacfAuthor Commented:
A great solution to my issue which greatly improves efficiency by eliminating manual tasks.

Thanks gowflow, much appreciated.
Andy
0
gowflowCommented:
Your welcome anytime. Pls do not hesitate to let me know if you need further help I will keep monitoring this question.
Rgds/gowflow
0
gowflowCommented:
Need any help ?
gowflow
0
andymacfAuthor Commented:
Hi gowflow

I will probably be posting a new question tomorrow to do with streamlining a function in excel, I can post a link here if you are interested.

I still have to present your work to my boss, so there may be a few tweaks needed and am happy to open new questions as necessary.

Andy
0
gowflowCommented:
ok no problem post any question here and if I can help I will be glad.
gowflow
0
andymacfAuthor Commented:
Hi gowflow

I would like your help again and will post a new question to this regard but I wanted to show you some information first so that I can find out from you how to phrase my question.

When we started this task, I said that the outputs would always be the same which at that time was accurate, however, I have been asked to add questions to the surveys and these new questions are structured differently so this is about how to encompass these changes whilst still getting relevant and accurate graphs.  I attach a document for you to have a look at, these are the changes that I have mentioned.

I look forward to hearing your thoughts
Andy
Survey-extra-fields.docx
0
gowflowCommented:
Yes no problem but You need to refresh my memory I looked at the solution I had prepared for you and know it was a survey and there was 3 buttons first to import the data from csv then to break the data into columns then to make up the graphs.

I see that the document you attached is completely different from the one you already had this one is more graphical output and the support is word. Will you need to read out of word document or csv ??? I need more clarifications on this as not clear also how you want to incorporate this into existing survey.

gowflow
0
andymacfAuthor Commented:
Hi gowflow
The document was just screenshots of the dropdown options that have been added to existing surveys, the output would still be in csvformat
0
gowflowCommented:
will the csv be additional to the existing csv or a new one altogether ?
gowflow
0
andymacfAuthor Commented:
Oops pressed the wrong button. Yes still in csv format, the key difference being that the number of outputs differs from the normal 4 that the others have
Andy
0
gowflowCommented:
ok I guess these are details. Ask a realted question to this one and just indicate what you need to achieve. I guess you also want to break up in 3 question Import, reports and graphs ??
gowflow
0
andymacfAuthor Commented:
I will try and post a new question tomorrow with the additional outputs with it being the end of the day here
Thanks Andy
0
gowflowCommented:
ok don't forget to put a link of the new question in here so I can help u with it. what time u have there ? it is 8:00 pm here are we on the same time ?
gowflow
0
andymacfAuthor Commented:
Will do 6pm here. Thanks again
0
gowflowCommented:
ok no problem ard what time ur time u expect to post tomorrow so I can look it up ?
gowflow
0
andymacfAuthor Commented:
Hi gowflow

Forgot I could access this from home, so I have created a new question, New Question

Regards
Andy
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Excel

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.