Excel Macro

[Galen_of_Edgewood]

Can I make a macro in Excel do this: I need it to copy columns of data into a text file with a header before each column, but only if it has a certain number of rows in the column? Is this possible? I'm tired of doing it by hand....

------------------
So they are no longer two, but one. -Matthew 19:6 (NIV)

[Klashe]

The only way that I can think of is save it as a tab delimited file (which is essiantially the table seperated by tabs) then open it in microsoft word and manually add the column names and delete the access columns. But since i've never done it with column names, tab delimited MIGHT actually save the names for you, if your lucky.
Sound like it will help?

------------------
Happiness is...

• P3-800 o/c to 896
• GeForce 2 MX
• SB mp3 5.1
• Klipsch ProMedias 2.400
• 30 gigger
• Viewsonic 19"

[Galen_of_Edgewood]

Originally posted by Klashe:
The only way that I can think of is save it as a tab delimited file (which is essiantially the table seperated by tabs) then open it in microsoft word and manually add the column names and delete the access columns. But since i've never done it with column names, tab delimited MIGHT actually save the names for you, if your lucky.
Sound like it will help?

Not really. I had thought of that, it probably won't speed me up anymore than I can do it now. Guess I'll just do it the old fashion way and cut and paste. Thanks for the idea...

------------------
So they are no longer two, but one. -Matthew 19:6 (NIV)

[turp182]

Klashe, do you still need help with this? I will post the script if so (give me about a day to put it together).

.( - The Sad Cyclops

------------------
We got it at the Wazmo...
www.thewazmo.com

[Klashe]

Originally posted by turp182:
Klashe, do you still need help with this? I will post the script if so (give me about a day to put it together).

.( - The Sad Cyclops

Thanks turp, but it's not me that need the favor, it's galen.



------------------
Happiness is...

• P3-800 o/c to 896
• GeForce 2 MX
• SB mp3 5.1
• Klipsch ProMedias 2.400
• 30 gigger
• Viewsonic 19"

[turp182]

Sorry about my confusion. Here's the macro that will do the requested things. It can be copy/pasted into a spreadsheet according to the comments at the top. You can paste all of the code or omit the comments (lines starting with '). Make sure to change the CONST lines to fit your data.

If you would like I could e-mail you a spreadsheet that already works. Let me know at [email protected].

Hope this helps.
Peter

Start of Code:
------------------------------
' GETTING STARTED...

' This will create a comma seperated text file for a defined block of data

' first, open the spreadsheet in question, or create a new one.
' Select Tools/Macros/Visual Basic Editor
' Double click on "This Workbook" in the Project Explorer on the left hand side to
' start editing
' If you are creating a new spreadsheet that will only contain the macro (it will run
' against the spreadsheet with the data), you will have to open up the new spreadsheet
' in order to run the macro. You will open the data spreadsheet,
' open the macro spreadsheet, select the data one, then choose Tools/Macro/Macros
' and select the one that's in the other sheet. The Macro will execute on the
' currently selected worksheet, so have the data opened and on your screen.

' this process may prompt you to save changes after it runs because it performs a save as
' procedure, just select no

Sub SaveAsText()

Dim RowCount, CurrRow As Long

Const MIN_ROWS = 10 ' constant for the minimum # of rows
Const NUM_COLS = 2 ' constant for how many columns to save
Const STARTING_ROW = 1 ' which row does the data start in
Const STARTING_COL = 1 ' which column does the data start in
Const TEXT_FILE = "c:\output.csv"

' first off, put the columns headers in the spreadsheet if at all possible.
' if this isn't possible, use this code:
'Rows(STARTING_ROW).Select
'Selection.Insert Shift:=xlDown
'Cells(STARTING_ROW, STARTING_COL).Value = "Header1"
'Cells(STARTING_ROW, STARTING_COL + 1).Value = "Header2"
' and so on

' count how many rows of data there are - this just goes down the first column of data
' looking for the next blank cell
CurrRow = STARTING_ROW - 1
RowCount = 0
Do
CurrRow = CurrRow + 1
RowCount = RowCount + 1
Loop While Cells(CurrRow, STARTING_COL).Value <> ""
' subtract one from each counter to get the actual values (kludge)
CurrRow = CurrRow - 1
RowCount = RowCount - 1

' were there enough rows to save the data?
If RowCount <= MIN_ROWS Then
MsgBox "There aren't enough rows of data, text file not created..."
Exit Sub
End If

' if we get here there was enough data to save...
Dim DataSheet, OutputSheet As Worksheet
Set DataSheet = ActiveSheet

' create a new temporary sheet to hold the output.
ActiveWorkbook.Sheets.Add after:=ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)
Set OutputSheet = ActiveWorkbook.Sheets(ActiveWorkbook.Sheets.Count)

' copy the data to the new sheet
DataSheet.Select
DataSheet.Range(Cells(STARTING_ROW, STARTING_COL), Cells(CurrRow, STARTING_COL + NUM_COLS - 1)).Select
Selection.Copy
OutputSheet.Select
Cells(1, 1).Select
ActiveSheet.Paste

' disable pop-up alerts
Dim DispAlerts As Boolean
DispAlerts = Application.DisplayAlerts
Application.DisplayAlerts = False

' save the new sheet as a text file
ActiveWorkbook.SaveAs Filename:=TEXT_FILE, FileFormat:=xlCSV, _
CreateBackup:=False

' clean up
DataSheet.Select
OutputSheet.Delete
Application.DisplayAlerts = DispAlerts

MsgBox "Output File : " & TEXT_FILE & " has been created..."
End Sub



------------------
We got it at the Wazmo...
www.thewazmo.com

[Klashe]

Originally posted by turp182:
Sorry about my confusion. Here's the macro that will do the requested things. It can be copy/pasted into a spreadsheet according to the comments at the top. You can paste all of the code or omit the comments (lines starting with '). Make sure to change the CONST lines to fit your data.

If you would like I could e-mail you a spreadsheet that already works. Let me know at [email protected].

Hope this helps.
Peter

Wow, it's awesome that you did that for someone that you don't know. You rock man!

------------------
Happiness is...

• P3-800 o/c to 896
• GeForce 2 MX
• SB mp3 5.1
• Klipsch ProMedias 2.400
• 30 gigger
• Viewsonic 19"

[turp182]

It's all about random acts of kindness and murder. In this case is was kindness...

------------------
We got it at the Wazmo...
www.thewazmo.com

[Adisharr]

Originally posted by turp182:
It's all about random acts of kindness and murder. In this case is was kindness...

You get a strong YES vote for 'was this a useful post?'


------------------
- No.. I don't know Moe..

[Galen_of_Edgewood]

Originally posted by Adisharr:
You get a strong YES vote for 'was this a useful post?'

Major agreement here. I've actually taken his code, which doesn't do exactly what I want it to do, and have been trying to get it to do it. Much help can be found for MS products (VB and MS Office products, especially) from this man.

------------------
So they are no longer two, but one. -Matthew 19:6 (NIV)

[turp182]

Thanks for the kudos.

As for finding information on this stuff, MS has what I consider to be the best support and help for it's programming tools than any company by far. It's called MSDN (there's also Technet and such), and it's 3 CDs of help files (or 1 DVD-ROM). But it costs momey to get it on media for a local install (it's also the default help files for Visual Studio, so if you get VS make sure to get the 2 MSDN CDs (it's an older version than current).

I'm rambling.

Anyway, MSDN is online for free. Just go to http://www.microsoft.com , click on Search at the top, and type in any strange phrase or error message (it's best at helping you determine what you are doing wrong).

I use MS's site probably 20+ times a day during the course of my day job.

There's also http://www.deja.com , which catalogs usenet messageboards, just type "visual basic" and your error and find what other people have had to say.

Those two sites are the true diamonds in product support. MS puts a lot of effort into supporting and helping it's developers, which goes a long way explaining their dominance (that and their monopolistic ways...).

I will also post some very good VB sites in a different topic. There are some gems there as well.

Whew.

Peter

------------------
We got it at the Wazmo...
www.thewazmo.com