Visual Basic IMport from files

Posted on 2014-09-12
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
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
LVL 45

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

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?
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.


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 45

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 45

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 45

Accepted Solution

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()
    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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…

734 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