1. Home
  2. Solidworks CAD Design software
  3. OT: Excel Guru - Quote/Comma delimited file creation

OT: Excel Guru - Quote/Comma delimited file creation

Hello,

I'm pushing a BOM from DBWorks to an ERP system.

The ERP adapter requires a quote/comma delilmited file in order to process the fields correctly.

I can generate an Excel file with the correct fields from DBWorks - they would appear as such (each space representing a new cell in Excel):

Part_Number Part_Description Manufactured etc...

What I need to do is generate a text file in the following format:

"Part_Number", "Part_Description", "Manufactured"

Getting the commas inserted is no problem since I can save the Excel sheet with the CSV extension.

My question is this - how do I create a macro that automatically adds the quotes to the beginning and end of every cell value. The columns and row sizes may change so I cannot "hard code" the macro for a given set.

Any suggestions would be appreciated since I will have to tackle this problem over the weekend. I would prefer not to spend too much time learning the ins and outs of Excel (Although I will if I have to).

Thanks in advance,

Len

Reply to
Len K. Mar
Loading thread data ...

If you write a loop to get each cell with a value, use this to add the quotes

Sheet1.Range("A1").Offset(Rows, Columns).Value = Chr(34) & Sheet2.Range("A1").Offset(Rows, Columns).Value & Chr(34)

Regards, Corey

Reply to
Corey Scheich

woops they should both be the same sheet.

Reply to
Corey Scheich

If you select all of the cells in the spreadsheet and set their format to text, when building a comma delimted file, the double-quotes will be placed automatically.

Best Regards,

Bob Hanson SW API and Excel Dude

Reply to
Bob Hanson

Hi Bob,

I must be doing something wrong because I can't get this to work.

I fill in three cells with a, b, c

Select the entire sheet and format cells to text.

Save file as CSV (Comma delimited).csv

I get the "filetype not compatible with multiple sheet" warning.

Can't for the life of me force a set of quotes for each cell value. Excel help file tells me to include a comma in each cell. The problem is the quote delimited file now includes the extra commas.

Any suggestions on what I'm doing wrong?

Len

Reply to
Len K. Mar

In a program like Word or I think even wordpad has it, use the replace feature in the edit menu to replace all spaces with ","

It's got a lot of human interaction, but perhaps someone else can automate this idea.

--Matt Schroeder

Reply to
Matt Schroeder

This is actually OT, since its pure Excel here, but since you managed to squeeze in the DBWorks reference, well ....

try this in excel. Select the range of cells you want to be encapsulated with quotes, and run the routine.

Sub QuoteRange() ' Runs thru selected range of cells in Active Excel Sheet ' and inserts quotes around strings Dim MySelection As Range, MyCell As Range Dim CellCount As Long, CellTally As Long Dim CellStr As String Dim QuoteChar As String: QuoteChar = Chr$(34)

Set MySelection = Application.Selection If MySelection Is Nothing Then Exit Sub CellCount = MySelection.Count If CellCount < 1 Then Exit Sub For CellTally = 1 To CellCount Set MyCell = MySelection.Item(CellTally) CellStr = "" & MyCell.Value ' force to string ' check for existing Quotation marks If (CellStr = "") Or (CellStr = QuoteChar) Then ' just double quotes CellStr = QuoteChar & QuoteChar Else If Left$(CellStr, 1) QuoteChar Then CellStr = QuoteChar & CellStr If Right$(CellStr, 1) QuoteChar Then CellStr = CellStr & QuoteChar End If MyCell.Value = CellStr Next CellTally

End Sub

Reply to
rocheey

A quick semi-manual example:

put a quote (") into cell D1--note that this is necessary because for excel a double quote has special meaning. If you were using most other characters, you could enter them directly in the formula below your data is in column A in cell B1, enter the following formula: =$d$1&A1&$d$1 Copy cell B1 to the rest of the cells in column B--column B should now be filled with quoted versions of the stuff in column A Select the entire column B, and select copy. Right click on cell C1, and select "paste special" and then values. This will copy column B to column C, but without the formulas Delete column A&B repeat as necessary Save as a CSV file

Reply to
Michael

PolyTech Forum website is not affiliated with any of the manufacturers or service providers discussed here. All logos and trade names are the property of their respective owners.