Solved

Excel VBScript to CSV

Posted on 2014-11-03
18
176 Views
Last Modified: 2014-11-08
I need help creating a macro/vbscript to take data from a worksheet and convert it into a csv file.  So I have the following data file format in Excel.

Raw Data
With the example above, there are some accounts I don't want to carry over to this csv file format as well as anything without a dash.

My output format, only pull account "500-40" and account "491-20" since I am filtering with account that does not have a dash or the account "3909-20".  

Can someone help with an example?  Let me specify in the VBScript which account(s) to not carry into the CSV file as well as any account without a dash?
0
Comment
Question by:holemania
[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
  • 7
  • 6
  • 5
18 Comments
 
LVL 29

Expert Comment

by:gowflow
ID: 40420530
When you mention VBScript you mean the VBScript language or could it be VBA ?

If VBA acceptable then we need you to pls post a sample workbook and will take it from there.
gowflow
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40420812
If no criteria were specified, you would expect account 800 to be output, since it does not contain a hyphen character?
0
 

Author Comment

by:holemania
ID: 40421939
Sorry yes VBA is what I was looking for.  Yes, if no criteria specified I would expect account 800 to be output.  However, I want to put in a criteria to only grab account with a hypen as well as exclude specific account per example.
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

 
LVL 45

Expert Comment

by:aikimark
ID: 40421973
I want to put in a criteria to only grab account with a hypen as well as exclude specific account per example.
It seems as though you have three or more filtering conditions.  So far, you have defined
* Everything without a hyphen (may be conditional, may be affected by existence/use of other filtering criteria)
* Everything WITH a hyphen (may be conditional, may be affected by existence/use of other filtering criteria)
* Exclusion list
* Inclusion list
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40422042
Waiting for the sample workbook !
gowflow
0
 

Author Comment

by:holemania
ID: 40422629
Attaching the excel workbook example.

To clarify here's the filter.

1)  Exclude if no hyphen in the account.
2)  Allow exclusion of 1 or more specific account.  Example, if excluding account "3909-20" and "491-20", both accounts should not show up.
Budget.xlsx
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40422779
Please reconcile these two filter criteria you've posted:
if no criteria specified I would expect account 800 to be output

Exclude if no hyphen in the account
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40422920
You could do this without any code by using the advancedfilter feature.  On a separate sheet, create one or more column headers with the string "account".  Below the first column, enter ="*-*"

For each account you want to exclude, add a not equals criteria below one of the account headers.
example: ="<>3909-20"

When applying the advancedfilter, choose the option to copy to a new location and specify an unused worksheet.  Select as many columns, two rows each, for the criteria.

You can save that destination worksheet as CSV.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423381
Aikimark is correct but I presume you want a one touch click solution as you first requested a VBA solution. So to combine Aikimark's Advancefilter flexibility and allow a macro to organize the work here is my solution.

1) I added a sheet Called Criteria (If you want to move this solution to your production workbook you will need to Create this Criteria Sheet and copy the Macro below to a Module or to your main sheet.
2) You add as many criterias as you want in the following format
first is equal sing the open quote and inside what you need then close quote
="*-*" will select or include all the hypen accounts.
="<>*-*" will EXCLUDE all the hyphen accounts
etc... I have there ="<>3909-20" and this will EXCLUDE the Account 3909-20
3) From Active sheet in the developer menu you choose the macro FilternSaveCSV it will filter the the activeshhet based on criteria in sheet Criteria and save it in the same directory giving it the name of the activesheet.

Please check it and let me know. The below workbook has the macro in it as well.

Option Explicit

Sub FilternSaveCSV()
Dim WS As Worksheet
Dim WSCopy As Worksheet
Dim WSCriteria As Worksheet
Dim MaxRow As Long
Dim CritRng As Range
Dim sCSV As String

Set WS = ActiveSheet
Set WSCriteria = Sheets("Criteria")
Worksheets.Add after:=Worksheets(Worksheets.Count)
Set WSCopy = ActiveSheet
Set CritRng = WSCriteria.Range(WSCriteria.Range("A1"), WSCriteria.Cells(2, WSCriteria.Range("A2").End(xlToRight).Column))

'---> Disable Events
With Application
    .EnableEvents = False
    .DisplayAlerts = False
    .ScreenUpdating = False
End With

'---> Apply Filter
WS.UsedRange.AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=CritRng, CopyToRange:=WSCopy.Range("A1")

'---> Create and Save CSV
WSCopy.Name = WS.Name & "-CSV"
WSCopy.Copy
ActiveWorkbook.SaveAs Filename:=WS.Name, FileFormat:=xlCSVWindows
ActiveSheet.UsedRange.EntireColumn.AutoFit
sCSV = ActiveWorkbook.FullName
ActiveWorkbook.Close savechanges:=True
WSCopy.Delete
WS.Activate


'---> Enable Events
With Application
    .EnableEvents = True
    .DisplayAlerts = True
    .ScreenUpdating = True
End With

'---> Remove Filter
MsgBox ("Filtered data saved as " & sCSV)

End Sub

Open in new window




gowflow
Budget-V01.xlsm
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40423460
the creation of the criteria range can be automated as well
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423491
@akimark
if your comment was addressed to me then I would say yes bearing in mind it is always what the asker mentioned but if it is something different everytime then the sheet will do.
gowlfow
0
 
LVL 45

Expert Comment

by:aikimark
ID: 40423505
@gowflow

It was a general comment, probably addressed to the OP, holemania.  
* The size of the exclusion list isn't clear from what has been written.
* The source of the exclusion list is unknown
* How many times this process needs to run
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40423571
@aikimark
Totally agree reason why maybe 'automation of criteria' is difficult to achieve in this context reason why I opted for the proposed solution in automating the maximum possible leaving the 'unkown' as user set.

Anyway, will leave OP, holemania to advise his/her comments.
gowflow
0
 

Author Comment

by:holemania
ID: 40426988
Gowflow,

That is awesome.  Works just as I want.  Is it possible to eliminate the header and also column B with the description?

Just Column A, C thru N?
0
 
LVL 29

Expert Comment

by:gowflow
ID: 40427094
Like you want to eliminate in the CSV produced
first row and Col B

Is that what you want ?
gowflow
0
 

Author Comment

by:holemania
ID: 40429360
Correct.
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
ID: 40429965
ok fine here it is.
gowflow
Budget-V02.xlsm
0
 

Author Closing Comment

by:holemania
ID: 40430617
Thank you.  That worked awesome.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
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…

739 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