Macro - VBA 2

[jewel]

hey all

I have been given a project to have a look at - have been using VBa in access but not too sure about excel.

They have a .txt file exported

Headings= Title: Clip Type: Blank: In point: Out Point: Duration
example lines as below

GOLF/RVO/6PM/08#1 GOLF/RVO/6PM/08#1 Deb Edits 01:27:00:00 01:27:21:11 00:00:21:11
HEROES1/JST/6PM/08 HEROES1/JST/6PM/08 Deb Edits 01:27:30:00 01:30:05:08 00:02:35:08
HURRICANES/SST/6PM/08 HURRICANES/SST/6PM/08 Deb Edits 01:30:30:00 01:32:10:03 00:01:40:03
OVERSTAYERS/TAK/6PM/08#1 OVERSTAYERS/TAK/6PM/08#1 Deb Edits 01:32:30:00 01:34:13:24 00:01:43:24
SCANNER/RCH/6PM/05 SCANNER/RCH/6PM/05 Deb Edits 01:34:30:00 01:35:56:23 00:01:26:23

the idea from what I gather is to open an Excel spreadsheet template and run a macro to look like below:

Title: In Point Duration
1 swimming iv xbf 07 01:00:00:00 00:06:46:10
2 TIPUORA/MRA/TEK/07 01:07:00:00 00:02:06:14
3 TRADEAID/SBR/BUS/07 01:09:30:00 00:03:11:10
4 TUHONO1/HGO/TEK/07 01:13:00:00 00:02:09:16

can someone point me in the right direction to start please.

1. Am I setting up a template first?
2. Do I then set up a macro or just add VBA to a command button
3. How do I account for extra spaces in txt file as data presents out of columns.

thanks for your help

Dianne

 

[PHV]

Have you tried the macro recorder when impoting the .txt file in your sheet ?

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SkipVought]

Dianne,

As PHV suggested, macro record the process of importing the txt file.

If you have trouble cleaning up the recorded code, post back.



Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

thanks both of you.

ok - If I start with a blank xls and record opening txt file and importing all 6 columns. (can do that) -

1. do I leave the macro recording while a delete 3 of the columns? (have left it running for now)

so I end up with the three relevant columns.

2. halfway down the list is text data which is irrelevant so also needs to be deleted - header pages etc from original app - when do I do this (it should always be in the same place each time)
(so do I record deleting this as part of the macro)

ok a couple of other things come to mind but let's get me this far.

cheers
Dianne

 

[123FakeSt]

Deleting the columns seems like it would work for you.

If it's the same EXACT rows to delete you can use the macro recorder. Start from the bottom up though, and it has to be the same total length every time. I doubt that this is the case.

If not, you will need to provide detail about how the garbage differs from the good stuff to devise a way to rid yourself of it. Usually one of the fields will be blank with the garbage, in which case go to thread707-748645. Another option is using a filter.

The early bird gets the worm, but the second mouse gets the cheese.

 

[jewel]

ok I have it doing mainly what I want but a couple of things need tidying up

I open the main spreadsheet (like a template with new header and column headings etc)which is the 2nd sheet.
On the first sheet I run the macro - which opens the text file - imports the date - removes the extra columns - removes the extra rows - then puts correct data from sheet 1 into sheet 2 in the correct columns

Point 1 - how do I close the text file after importing the data?

Point 2 - What happens if the data I next import eg next week is more or less than this one? I'm not sure I've covered for this.

Is it better for me to post the recorded code here?


cheers
Dianne

 

[SkipVought]

Point 1 - how do I close the text file after importing the data?

You must do a SaveAs.

Point 2 - What happens if the data I next import eg next week is more or less than this one? I'm not sure I've covered for this.

More rows -- no problem. Caveat: if rows you will be deleting are different rows is a problem UNLESS you can logically define the rows to delete.

More columns -- problem using recorded macro

Is it better for me to post the recorded code here?[/code]

Yes, we can help you generalize your code.





Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

hey thanks heaps Skip
the following is my code.

1. so where do I add the saveas to ALC60-2700.txt

2. will always be column B:C deleted then when data moved to B it will be C deleted.

3. So when text data headings removed (these will always be the same place) there could be more rows in the columns to copy to sheet 2 (not sure how I handle this)

thanks
Dianne


Sub importtext()
'
' importtext Macro
' Macro recorded 12/05/2004 by Dianne Dalton

Workbooks.OpenText Filename:="C:\testing\ALC60 - 2700.txt", Origin:= _
xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
Columns("A:A").ColumnWidth = 25.71
Columns("B:B").ColumnWidth = 25.71
Columns("C:C").ColumnWidth = 25.71
Columns("D").ColumnWidth = 13.71
Columns("E:E").ColumnWidth = 13.71
Columns("F:F").ColumnWidth = 13.71
Columns("B:C").Select
Selection.Delete Shift:=xlToLeft
Columns("C:C").Select
Selection.Delete Shift:=xlToLeft
Range("C4").Select
ActiveCell.FormulaR1C1 = "Duration"
Range("B4").Select
ActiveCell.FormulaR1C1 = "In Point"
Range("D4").Select
ActiveWindow.SmallScroll Down:=24
Rows("41:48").Select
Selection.Delete Shift:=xlUp
Range("A1:C51").Select
Selection.Copy
Windows("single line4a.xls").Activate
Range("A1").Select
ActiveSheet.Paste
Range("D1").Select
Sheets("single line4").Select
Range("C9").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
Range("D9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
Range("E9").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
Range("C9:E9").Select
Selection.AutoFill Destination:=Range("C9:E38"), Type:=xlFillDefault
Range("C9:E38").Select
Range("C9:E38").Select
Range("C10").Activate
ActiveWindow.SmallScroll Down:=16
Range("C41").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
Range("D41").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
Range("E41").Select
ActiveCell.FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
Range("C41:E41").Select
Selection.AutoFill Destination:=Range("C41:E57"), Type:=xlFillDefault
Range("C41:E57").Select
Range("F56").Select
End Sub

 

[SkipVought]

Sub importtext()
'
' importtext Macro
' Macro recorded 12/05/2004 by Dianne Dalton
' modified by SkipVought Tek-Tips 5/12/2004

  Workbooks.OpenText Filename:="C:\testing\ALC60 - 2700.txt", Origin:= _
        xlWindows, StartRow:=1, DataType:=xlDelimited, TextQualifier:= _
        xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, Semicolon:=False, _
        Comma:=False, Space:=False, Other:=False, FieldInfo:=Array(Array(1, 1), _
        Array(2, 1), Array(3, 1), Array(4, 1), Array(5, 1), Array(6, 1))
    Columns("A:C").ColumnWidth = 25.71
    Columns("D:F").ColumnWidth = 13.71
    Columns("B:C").Delete Shift:=xlToLeft
    Columns("C:C").Delete Shift:=xlToLeft
    Range("C4").Value = "Duration"
    Range("B4").Value = "In Point"
    Rows("41:48").Delete Shift:=xlUp
    Range("A1:C51").Copy _
        Destination:=Workbooks("single line4a.xls").Sheets("single line4").Range("A1")
    With Sheets("single line4")
        .Activate
        .Range("C9").FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
        .Range("D9").FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
        .Range("E9").FormulaR1C1 = "=Sheet1!R[-4]C[-2]"
        .Range("C9:E9").AutoFill Destination:=Range("C9:E38"), Type:=xlFillDefault
        .Range("C41").FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
        .Range("D41").FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
        .Range("E41").FormulaR1C1 = "=Sheet1!R[-6]C[-2]"
        .Range("C41:E41").AutoFill Destination:=Range("C41:E57"), Type:=xlFillDefault
        .Range("F56").Select
    End With
    ActiveWorkbook.SaveAs Filename:="C:\testing\ALC60-2700.txt"
End Sub

How's this?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

ok Skip

erroring here

Range("A1:C51").Copy _
Destination:=Workbooks("single line4a.xls").Sheets("single line4").Range("A1")

Runtime error 1004
cannot change part of a merged cell

??
Dianne

 

[SkipVought]

Workbooks("single line4a.xls").Sheets("single line4")

Do you have merged cells on this sheet?

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

the data comes from the .txt into the first sheet of this spreadsheet - then merges into cells in like a template

Title: In Point Duration
1 0 0 0
2 0 0 0
3 0 0 0
4 0 0 0
5 0 0 0

cheers
Dianne

 

[SkipVought]

That's NOT what I asked...

Do you have merged cells on this sheet? ie the Sheet that you are copying to???

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

ok

no the 1st sheet is blank except for a command button that activates the macro

Dianne

 

[SkipVought]

I asked about ...

Workbooks("single line4a.xls").Sheets("single line4")

!!!

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

in Workbooks("single line4a.xls").Sheets("single line4")

in the sheet (single line4)

this is preset as explained - so we have headings already and no's down the side - so the 0's get replaced with the data coming from sheet 1

Title: In Point Duration
1 0 0 0
2 0 0 0
3 0 0 0
4 0 0 0
5 0 0 0

I'm sorry I'm not sure what else to say
thanks
Dianne

 

[SkipVought]

The ERROR

Runtime error 1004
cannot change part of a merged cell

indicates that when the macro tries to PASTE into that sheet, starting in A1, SOMEWHERE in the paste area (the number of rows and columns in the COPY area) there is a MERGED CELL.

Skip,

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jewel]

thanks for your help Skip it is much appreciated and I am learning heaps - ok that part of my code worked -so until I have time to sit down and match the differences I will leave it the long way. As I have to have this in shortly.

now the saveas part

I just need to close this txt file - I don't want to save

thanks
Dianne

 

[123FakeSt]

You should be able to record that. It would resemble this:

Change:

ActiveWorkbook.SaveAs Filename:="C:\testing\ALC60-2700.txt"

To:

Application.DisplayAlerts = False
Windows.Activate("C:\testing\ALC60-2700.txt")
ActiveWorkbook.Close
Application.DisplayAlerts = True

--Rusty


The early bird gets the worm, but the second mouse gets the cheese.

 

[123FakeSt]

As for your merged cells, make sure Workbooks("single line4a.xls").Sheets("single line4") has no merged cells (select them all and uncheck the 'Merged Cells' on the alignment tab).

If your import is creating it (never experienced that though) you will need this in your code before the copy.

Cells.Select
With Selection
.MergeCells = False
End With

--Rusty

The early bird gets the worm, but the second mouse gets the cheese.