Excel VBScript to CSV

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?
holemaniaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gowflowCommented:
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
aikimarkCommented:
If no criteria were specified, you would expect account 800 to be output, since it does not contain a hyphen character?
0
holemaniaAuthor Commented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

aikimarkCommented:
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
gowflowCommented:
Waiting for the sample workbook !
gowflow
0
holemaniaAuthor Commented:
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
aikimarkCommented:
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
aikimarkCommented:
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
gowflowCommented:
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
aikimarkCommented:
the creation of the criteria range can be automated as well
0
gowflowCommented:
@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
aikimarkCommented:
@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
gowflowCommented:
@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
holemaniaAuthor Commented:
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
gowflowCommented:
Like you want to eliminate in the CSV produced
first row and Col B

Is that what you want ?
gowflow
0
holemaniaAuthor Commented:
Correct.
0
gowflowCommented:
ok fine here it is.
gowflow
Budget-V02.xlsm
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
holemaniaAuthor Commented:
Thank you.  That worked awesome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

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.