Solved

Grab flatfile data to xlsm & make More Copies

Posted on 2013-01-19
10
274 Views
Last Modified: 2013-01-22
Hello All,

I am trying to figure out the best way via VBA to do this:
Regarding code: FSO Method is greatly preferred over f = dir ()

I have a main.xlsm file which will read a folder and

1.      Go through each .csv file in that folder.
2.      open up a csv file to get the csv data and copy it over to cell C8 of sheet1 of main.xlsm file
3.      Then saveAS (in the same folder as the xlsm file)  the xlsm file with the name of the csv file that it copied data from
4.      Closes the csv file and moves on to the next csv in the target folder

So for example: If there were three csv files in the folder : csv1.csv,csv2.csv,csv3.csv in the source folder, then after the operation, there  are three xlsm files in the folder containing the main.xlsm: csv1.xlsm,csv2.xlsm,csv3.xlsm

Thanks
main.xlsm
csv3.csv
csv2.csv
csv1.csv
0
Comment
Question by:Rayne
[X]
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
  • 5
10 Comments
 

Author Comment

by:Rayne
ID: 38797722
there could be a possible 200 csv files in the source folder
0
 
LVL 26

Accepted Solution

by:
redmondb earned 500 total points
ID: 38797843
Hi, Rayne.

Please see attached. The code is...
Sub Button1_Click()
Dim strPath     As String
Dim strFolder   As String
Dim strNewName  As String
Dim strFileName As String
Dim objF1       As Object
Dim objFS       As Object
Dim objFiles    As Object
Dim objFolder   As Object
Dim wbNew       As Worksheet
Dim wCSV        As Workbook

strPath = ThisWorkbook.Path & "\CSV"

Application.ScreenUpdating = False

Set objFS = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFS.GetFolder(strPath)
Set objFiles = objFolder.Files
    
For Each objF1 In objFiles

    strFileName = objF1.Name
    If Len(objF1.Name) > 4 Then
        If LCase(Mid(objF1.Name, Len(objF1.Name) - 3)) = ".csv" Then
            
            Set wCSV = Workbooks.Open(strPath & "\" & objF1.Name)
            
            Set wbNew = ThisWorkbook.Worksheets.Add
            
            If Sheet_Exists(wCSV.Name, ThisWorkbook.Name) Then
                MsgBox ("""" & wCSV.Name & """ already exists - Date+Time will be appended to give a unique name")
                wbNew.Name = Mid(wCSV.Name, 1, 18) & "_" & Format(Now(), "yymmddhhnnss")
            Else
                wbNew.Name = Mid(wCSV.Name, 1, 31)
            End If
            
            wCSV.Sheets(1).UsedRange.Copy Destination:=wbNew.Range("A8")
            
            strNewName = Mid(wCSV.Name, 1, Len(wCSV.Name) - 3) & "xlsm"
            If Dir(strPath & "\" & strNewName) <> "" Then
               MsgBox ("""" & strNewName & """ already exists - Date+Time will be appended to give a unique name")
                strNewName = Mid(wCSV.Name, 1, Len(wCSV.Name) - 3) & "_" & Format(Now(), "yymmddhhnnss") & ".xlsm"
            End If
        
            wCSV.SaveAs Filename:=strPath & "\" & strNewName, FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
            
            wCSV.Close savechanges:=False
                        
        End If
        
   End If
   
Next

Set objF1 = Nothing
Set objFiles = Nothing
Set objFolder = Nothing
Set objFS = Nothing
End Sub

Function Sheet_Exists(xSheet_Name As String, Optional xBook As String) As Boolean

If xBook = "" Then xBook = ActiveWorkbook.Name

Sheet_Exists = False

On Error Resume Next
    Sheet_Exists = (Workbooks(xBook).Sheets(xSheet_Name).Name = xSheet_Name)
On Error Resume Next

End Function

Open in new window

Regards,
Brian.
main-V2.xlsm
0
 

Author Comment

by:Rayne
ID: 38797904
Perfect Brian,

you are always there to help :)
0
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 

Author Closing Comment

by:Rayne
ID: 38797905
thank you
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38797923
Thanks, Rayne - always glad to help!

(Out of curiosity, why the lack of love for Dir()?!)
0
 

Author Comment

by:Rayne
ID: 38798925
Hello Brian,

Sure I will be glad to share the thought :)

 In this question:
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_27985710.html

I tried the expert's F = DIR() idea but when I tested it, I found it kept looping over the same exact  file in the directory THAN moving onto next file...Not sure why it did that same file infinite loop on my computer. So instead of spending more time on why its not working, I gave up and used the FSO and it worked great.

Rayne
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38799656
Thanks, Rayne. I'll have a look, but it'll be a couple of days.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38803591
Rayne,

I've looked at that macro and I can't see any problems with it. (I even replaced the updates with Debug.Print and got exactly the results I expected.)

The key ideas are...
(1) Run Dir() once with parameters. You need to check for a blank result - that means the file (or files) don't exist.
(2) Repeatedly run Dir() without parameters. Again, you need to check for a blank result - that means there are no more files and so you need to break out of the loop.

There are few ways that you could accidentally cause a loop, but, seeing that the there is no "Option Explicit" , I think it very possible that "Fil" was misspelled - "FiI" or "Fi1", perhaps.

Another possibility would be to include a  file name in the "looping" Dir().

Finally, any chance that the wrong (or no) variable was used for controlling the loop?

Regards,
Brian.
0
 

Author Comment

by:Rayne
ID: 38804032
Hello Brian,

Thank you for detailed analysis. Maybe one of the other went wrong :(

Sure I will keep that in mind next time. Maybe ask a new question next time with especially DIR() stuff and notify you

Thank you :) again for your effort  to help me
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38808402
Glad to help, Rayne!

(Rather than pushing the expert to use Dir(), you'll probably get a better answer by letting him/her choose!)
0

Featured Post

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
This article helps those who get the 0xc004d307 error when trying to rearm (reset the license) Office 2013 in a Virtual Desktop Infrastructure (VDI) and/or those trying to prep the master image for Microsoft Key Management (KMS) activation. (i.e.- C…
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

688 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