Solved

Excel VBScript to CSV

Posted on 2014-11-03
18
177 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 30

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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 
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 30

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 30

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 30

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 30

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 30

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 30

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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone 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

This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

717 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