Visual Basic IMport from files

Posted on 2014-09-12
Medium Priority
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

     Application.ScreenUpdating = False
     Application.EnableEvents = False
     Application.DisplayAlerts = False

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

     If MsgBox("Clear the old data first?", vbYesNo) = vbYes Then
         NC = 1
         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
             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
         'move file to "imported" folder
             Name fPath & fName As fPathDone & fName
         'ready next filename (if any)
             fName = Dir
         'Next column
             NC = NC + 1

     Application.DisplayAlerts = True
     Application.EnableEvents = True
     Application.ScreenUpdating = True

 'restores users original working path
     ChDir OldDir
 End Sub

Open in new window

Question by:AlfonsoPina
  • 5
  • 4
LVL 46

Expert Comment

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?
LVL 33

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 ?
LVL 46

Expert Comment

ID: 40320823
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.

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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


Author Comment

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.
LVL 46

Expert Comment

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.

Author Comment

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.
LVL 46

Expert Comment

ID: 40322319

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?

Author Comment

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.
LVL 46

Accepted Solution

aikimark earned 2000 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()
    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:

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

Author Closing Comment

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

Featured Post

Managing Security Policy in a Changing Environment

The enterprise network environment is evolving rapidly as companies extend their physical data centers to embrace cloud computing and software-defined networking. This new reality means that the challenge of managing the security policy is much more dynamic and complex.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The SignAloud Glove is capable of translating American Sign Language signs into text and audio.
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
In this fourth video of the Xpdf series, we discuss and demonstrate the PDFinfo utility, which retrieves the contents of a PDF's Info Dictionary, as well as some other information, including the page count. We show how to isolate the page count in a…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

621 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