Solved

Excel VBScript to CSV

Posted on 2014-11-03
18
167 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
  • 7
  • 6
  • 5
18 Comments
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 45

Expert Comment

by:aikimark
Comment Utility
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
Comment Utility
Waiting for the sample workbook !
gowflow
0
 

Author Comment

by:holemania
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 45

Expert Comment

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

Expert Comment

by:gowflow
Comment Utility
@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
Comment Utility
@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
Comment Utility
@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
Comment Utility
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
Comment Utility
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
Comment Utility
Correct.
0
 
LVL 29

Accepted Solution

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

Author Closing Comment

by:holemania
Comment Utility
Thank you.  That worked awesome.
0

Featured Post

What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Approximate matching with VLOOKUP and MATCH seems to me to be a greatly under-used technique, and one which is vital for getting good performance out of large lookups. Until recently I would always have advised using an exact match for simplicity an…
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now