Solved

Visual Basic IMport from files

Posted on 2014-09-12
11
544 Views
Last Modified: 2014-09-15
I need to adjust the following script that I found to import from multiple .csv files to one excel sheet. each of the csv's have only one column ranging from about 65 - 100 rows. I would like to have a single sheet and not a workbook with multiple sheets. I should have about 111 -118 csv files to import from. The line I keep getting the error on is the          'Copy data                  Range("F1:F8").Copy wbkNew.Sheets("Master").Cells(1, NC)   Line.

Option Explicit

Sub Consolidate()
  'Open all Excel files in a specific folder and merge F1:F8 master sheet
  'Moves imported files into another folder
  'JBeaucaire (12/7/2009)     (2007 compatible)
  Dim fName As String, fPath As String, fPathDone As String, OldDir As String
  Dim LR As Long, NC As Long
  Dim wbkOld As Workbook, wbkNew As Workbook, ws As Worksheet

 'Setup
     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.DisplayAlerts = False

     Set wbkNew = ThisWorkbook
     wbkNew.Activate
     'Sheets("Master").Activate   'optional, edit as needed

     If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
         Cells.Clear
         NC = 1
     Else
         NC = Cells(1, Columns.Count).End(xlToLeft).Column + 1
     End If

 'Path and filename
     OldDir = CurDir                 'memorizes the user's current working path
     fPath = "\\<servername>\share\General_Use_Documents\GPOReporting\you\"              'path with import files
     fPathDone = "\\<servername>\share\General_Use_Documents\GPOReporting\you\Imported\" 'path to move imported files into
     ChDir fPath                     'set current dir to import folder
     fName = Dir("*.csv")            'start a list of import files

 'Import files one at a time
     Do While Len(fName) > 0
         'import file
             ActiveWorkbook.Worksheets.Add
             With ActiveSheet.QueryTables.Add(Connection:="TEXT;" & fName, Destination:=Range("A1"))
                 .Name = "file1"
                 .FieldNames = True
                 .RowNumbers = False
                 .FillAdjacentFormulas = False
                 .PreserveFormatting = True
                 .RefreshOnFileOpen = False
                 .RefreshStyle = xlInsertDeleteCells
                 .SavePassword = False
                 .SaveData = True
                 .AdjustColumnWidth = True
                 .RefreshPeriod = 0
                 .TextFilePromptOnRefresh = False
                 .TextFilePlatform = 437
                 .TextFileStartRow = 1
                 .TextFileParseType = xlDelimited
                 .TextFileTextQualifier = xlTextQualifierDoubleQuote
                 .TextFileConsecutiveDelimiter = True
                 .TextFileTabDelimiter = True
                 .TextFileSemicolonDelimiter = False
                 .TextFileCommaDelimiter = True
                 .TextFileSpaceDelimiter = False
                 .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1)
                 .TextFileTrailingMinusNumbers = True
                 .Refresh BackgroundQuery:=False
             End With

         'Copy data
             Range("F1:F8").Copy wbkNew.Sheets("Master").Cells(1, NC)
         'delete imported sheet
             ActiveSheet.Delete
         'move file to "imported" folder
             Name fPath & fName As fPathDone & fName
         'ready next filename (if any)
             fName = Dir
         'Next column
             NC = NC + 1
     Loop

 'Cleanup
     Cells.Columns.AutoFit
     Application.DisplayAlerts = True
     Application.EnableEvents = True
     Application.ScreenUpdating = True

 'restores users original working path
     ChDir OldDir
 End Sub

Open in new window

0
Comment
Question by:AlfonsoPina
  • 5
  • 4
11 Comments
 
LVL 45

Expert Comment

by:aikimark
ID: 40320766
If there is only a single column of data in the CSV file, there would only be data in column A.  Why are you copying data from column F?
0
 
LVL 32

Expert Comment

by:Robberbaron (robr)
ID: 40320813
1/ what error do you get ?
2/ and further to aikimark comment,  you only appear to copy the first 8 lines of the imported data..

can you post a sample of a csv file maybe first 5 or 6 lines of a couple ?
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40320823
@robr
I'm not convinced that the posted script is a good starting point.  I think the optimal solution will require us to determine the actual needs of the import.

==========
@AlfonsoPina
Let's drop back to the requirements of the import
1. import data from multiple csv files in a single directory
2. imported data is placed in a single worksheet
3. all the csv files have a single column of data

Do the csv files have a header row?
What is the type of data in the column?
Is there any particular order in which the CSV files should be processed?
Where will each CSV file's data be placed in relation to the prior file's data?
0
 

Author Comment

by:AlfonsoPina
ID: 40320826
Ok, every one of these comments are very good. I'll post an example shortly.  I just would like to reiterate that I borrowed the script from somewhere else. I have actually changed the F to an A. At one point I added 120 to the number instead of 8. Either way  .  I still got an error. I'll post the error.
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40320832
If this isn't your code or written to solve your problem, I'm now more convinced than I was about the need to start from the requirements.

I borrowed the script
Not the verb I would use.
0
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

 

Author Comment

by:AlfonsoPina
ID: 40322290
ok, attached is something I just created to show what we want. I use a simple script to pull data from all of the workstations on our domain, test1.xlsx is the pull from pc1 (theoretically), test2 from pc2 etc. The data is always in a single column. I would like to import all of these from the individual excel sheets into one sheet in the all software.xlsx file. so then I would have a sheet with about 118 columns. on my actual network there are approximately 118 - 125 workstations. so then the sheets would be test1.xlsx thru test120.xlsx.

back to the original script, please disregard it. I used what I could find, I do not know how to create macro's like this. I am simply trying to do a job at work that I have never actually done before. I'm a systems administrator and a network administrator, I like hardware more than software. but I have to get my software under control now.
test2.xlsx
all-software.xlsx
test1.xlsx
test3.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40322319
@Alfonso

Thanks for uploading those files.

Since you're in Ops (or DevOps), I would recommend you look at Powershell.  It has been part of the OS since at least WinXP.

What do you need to do with the data after it has been imported?
0
 

Author Comment

by:AlfonsoPina
ID: 40322326
well, we use powershell and we "get" the information that is on those individual machines. now we need to put them into one sheet, compare with a master "allowable" list of software and then we have a script that uninstalls the offending software. so I have a set of formulas that compares and then deletes the duplicates. I want to take the remaining, which would obviously be the offending list, and set my uninstall script to that list. I run it on all machines, the script skips the ones that doesn't have it installed.

we are using SCCM but we are a remote site and so we have limited access to the SCCM services at our location. so instead of going back and forth every day with multiple items for a single person who, at the HQ's locations, to uninstall from our location. he works on scripts for install/uninstall and updates for multiple locations world wide. I am trying to be proactive and skip ahead to completing the tasks.
0
 
LVL 45

Accepted Solution

by:
aikimark earned 500 total points
ID: 40322335
The solution is going to look something like this:
Option Explicit

Public Sub Q_28517522()
    Dim strFile As String, strData As String
    Dim intFN As Integer
    Dim rng As Range
    Dim wks As Worksheet
    Dim vData As Variant
    Const cPath As String = "C:\Users\Mark\Downloads\Q_28517522\"
    Set wks = ActiveSheet
    Set rng = wks.Range("A1")
    strFile = Dir(cPath & "*.csv")
    Application.ScreenUpdating = False
    Do Until Len(strFile) = 0
        intFN = FreeFile
        Open cPath & strFile For Input As #intFN
        strData = Input(LOF(intFN), #intFN)
        Close intFN
        vData = Split(strData, vbCrLf)
        wks.Range(rng, rng.Offset(UBound(vData))).Value = WorksheetFunction.Transpose(vData)
        rng.Value = Left(strFile, Len(strFile) - 4)
        Set rng = rng.Offset(0, 1)
        strFile = Dir()
    Loop
    Application.ScreenUpdating = True
End Sub

Open in new window

If you want to know more about this array push into a worksheet, read my article on the subject:
http:A_2253-Fast-Data-Push-to-Excel.html

=========
edited to remove the ".csv" from row 1 data
0
 

Author Closing Comment

by:AlfonsoPina
ID: 40323720
Thanks, I adjusted it after watching the videos and viewing instructions.  We finally have a list, now to make my users cry a little:-D
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Query Missing Money orders... 6 73
How to make an ADE file by code? 11 42
cannot get subtotal to work 8 18
Name Rotation 11 30
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This is an explanation of a simple data model to help parse a JSON feed
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

746 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

11 Experts available now in Live!

Get 1:1 Help Now