Macro to separate, concatenate, and change sheet names

I am brand new to Macros - in fact, I really do not know what I am doing with them at all.

I would like to see if I can create a macro to even more simplify the repetitive tasks that I need to do. This will reference the Macro solution that was given to me in Post #4 on this link: http://www.excelforum.com/excel-gene...ml#post3679700

I gave a simplified version of what I am actually trying to do because I didn't expect as awesome of an answer as I got - and, it seems, with using a Macro, I could get Excel to do even more for me.

Here is a table of exactly what I am looking at


SHEET 1

A	B	C	D	E
Date&Time	Ask Price	Bid Price	Ask Volume	Bid Volume
2007.03.25 21:00:03.496	1.64845	1.64785	1	1

That is the basic setup and it goes for about 500,000 rows and I have almost 5,500 workbooks. So the idea of automating my tasks as much as possible is extremely appealing

Following the pattern on the link that I gave above, I would like the following:

ASK (This is Sheet #2 having been renamed)

A	B
20070325	210003;1.64845;1

(concatenation of Time, Ask Price, & Ask Volume...all separated only by a ";")

BID (Rename of Sheet 3)

A	B
20070325	210003;1.64785;1

(concatenation of Time, Bid Price, & Bid Volume...all separated only by a ";")

And, with the answer, could you also share with me precisely how I am supposed to enter this Macro into excel and also if there is some way of being able to this in a batch, rather than each file individually?

thank you very much and I deeply appreciate any help that anyone is able to give to me.

Hi, JGSmith,

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution.

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

Ciao,
Holger

Hi, JGSmith,

I´ll take the easy part first about inserting VBA code

How to install your new code

1. Copy the Excel VBA code
2. Select the workbook in which you want to store the Excel VBA code
3. Press Alt+F11 to open the Visual Basic Editor
4. Choose Insert > Module
5. Edit > Paste the macro into the module that appeared
6. Close the VBEditor
7. Save your workbook (Excel 2007+ select a macro-enabled file format, like *.xlsm)

To run the Excel VBA code:

1. Press Alt-F8 to open the macro list
2. Select a macro in the list
3. Click the Run button

For the other question I would like to ask for a workbook with some samples instead of any pictures. The code could be placed in one workbook and all files could be opened, procesed and closed and saved without having to install the code there (this could be either a workbook or an AddIn).

Ciao,
Holger

Hi, JGSmith,

this is only for the file you provided to run a test and see if things head into what you finally want.

Sub EF1007981()
Dim lngCounter As Long
Dim var As Variant
Dim wsBid As Worksheet
Dim wsAsk As Worksheet
Dim ws As Worksheet

Const cstrPATH As String = "C:\HaHoBe\Downloads\"
Const cstrFILE As String = "Default File.csv"

Workbooks.Open Filename:=cstrPATH & cstrFILE
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 2)), _
    TrailingMinusNumbers:=True

Range("G1:H1").Value = Array("Date", "Time")

Set ws = ActiveSheet
Set wsAsk = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsAsk.Name = "Ask"
Set wsBid = Worksheets.Add(After:=Worksheets(Worksheets.Count))
wsBid.Name = "Bid"

Application.ScreenUpdating = False
With ws
  For lngCounter = 2 To .Range("A" & Rows.Count).End(xlUp).Row
    var = Split(.Cells(lngCounter, "A").Value, " ")
    .Cells(lngCounter, "G").Value = Format(var(0), "YYYYMMDD")
    .Cells(lngCounter, "H").Value = Format(Left(var(1), 8), "hhnnss")
    wsAsk.Cells(lngCounter - 1, "A").Value = .Cells(lngCounter, "G").Value
    wsAsk.Cells(lngCounter - 1, "B").Value = _
      .Cells(lngCounter, "H").Value & ";" & .Cells(lngCounter, "B").Value & ";" & 1   'not sure where that value comes from
    wsBid.Cells(lngCounter - 1, "A").Value = .Cells(lngCounter, "G").Value
    wsBid.Cells(lngCounter - 1, "B").Value = _
      .Cells(lngCounter, "H").Value & ";" & .Cells(lngCounter, "C").Value & ";" & 1   'not sure where that value comes from
  Next lngCounter
End With
ActiveWorkbook.SaveAs cstrPATH & "JGSmith processed " & Format(Now, "YYYYMMDD_hhnnss") & ".xlsx", FileFormat:=51
ActiveWorkbook.Close False

Application.ScreenUpdating = True
Set wsBid = Nothing
Set wsAsk = Nothing
Set ws = Nothing

End Sub

Ciao,
Holger

Hi, JGSmith,

you don´t have the path

"C:\HaHoBe\Downloads\"

on your computer - not so surprising.

You would need to adapt the drive and folders as well as the name of the file to suit your requirements. You could use Application.GetOpenFilename or BrowseForFolder to get the information or place this information in cells on a worksheet but for the moment you should alter the information for the constants.

Ciao,
Holger

Hi, JGSmith,

if I remember correctly any textfile will only consist of one sheet, so could you please explain to me how to add sheets to these files? Check the folder and see if any processed file has been saved there, maybe open that and have a glimpse at what this file may look like.

Ciao,
Holger

Hi, JGSmith,

have a look at this:

Sub EF1007981_2()
Dim lngCounter As Long
Dim var As Variant
'Dim wsBid As Worksheet
'Dim wsAsk As Worksheet
Dim ws As Worksheet

Const cstrPATH As String = "C:\HaHoBe\Downloads\"
Const cstrFILE As String = "Default File.csv"

Workbooks.Open Filename:=cstrPATH & cstrFILE
Columns("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Comma:=True, _
    FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 2)), _
    TrailingMinusNumbers:=True

Range("G1:J1").Value = Array("Date", "Time", "Ask", "Bid")

Set ws = ActiveSheet
'Set wsAsk = Worksheets.Add(After:=Worksheets(Worksheets.Count))
'wsAsk.Name = "Ask"
'Set wsBid = Worksheets.Add(After:=Worksheets(Worksheets.Count))
'wsBid.Name = "Bid"

Application.ScreenUpdating = False
With ws
  For lngCounter = 2 To .Range("A" & Rows.Count).End(xlUp).Row
    var = Split(.Cells(lngCounter, "A").Value, " ")
    .Cells(lngCounter, "G").Value = Format(var(0), "YYYYMMDD")
    .Cells(lngCounter, "H").Value = Format(Left(var(1), 8), "hhnnss")
    .Cells(lngCounter, "I").Value = _
      .Cells(lngCounter, "H").Value & ";" & .Cells(lngCounter, "B").Value & ";" & 1   'not sure where that value comes from
    .Cells(lngCounter, "J").Value = _
      .Cells(lngCounter, "H").Value & ";" & .Cells(lngCounter, "C").Value & ";" & 1   'not sure where that value comes from
  Next lngCounter
End With
'the following line is for testing purposes:
ActiveWorkbook.SaveAs cstrPATH & "JGSmith processed " & Format(Now, "YYYYMMDD_hhnnss") & ".xlsx", FileFormat:=51
'if the file is going to be saved under the same name exchange False with True on the next line
ActiveWorkbook.Close False

Application.ScreenUpdating = True
'Set wsBid = Nothing
'Set wsAsk = Nothing
Set ws = Nothing

End Sub

Manipulation will take place on the same sheet, you could either leave empty columns between the areas or use a code to export the desired columns into textfiles.

Ciao,
Holger

Hi, JGSmith,

if you pasted the new procedure over the existing one you would need to adapt the correct name in the calling procedure.

Ciao,
Holger