Visual Basic IMport from files

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

AlfonsoPinaSr. Systems AdministratorAsked:
Who is Participating?
aikimarkConnect With a Mentor Commented:
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
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?
Robberbaron (robr)Commented:
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 ?
Firewall Management 201 with Professor Wool

In this whiteboard video, Professor Wool highlights the challenges, benefits and trade-offs of utilizing zero-touch automation for security policy change management. Watch and Learn!

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?
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
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.
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.
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
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.

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?
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
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.
AlfonsoPinaSr. Systems AdministratorAuthor Commented:
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
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.

All Courses

From novice to tech pro — start learning today.