Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


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
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 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 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 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?
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.


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

Build and deliver software with DevOps

A digital transformation requires faster time to market, shorter software development lifecycles, and the ability to adapt rapidly to changing customer demands. DevOps provides the solution.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

664 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