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

 

[jewel]

thanks Rusty

but have added that but - the .txt file is still open in the background?

ok one last thing as well

instead of say range A1:C51 which I copy etc - what if next month = A1: C60???

my current code does not account for that?

cheers
Dianne

 

[SkipVought]

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

Skip,

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

 

[123FakeSt]

What is the application that the text file is open in? Is it Excel or is it Notepad, Word etc.

I think i cut and pasted too much, you don't want the path. Should be....

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

Try commenting out the DisplayAlerts first so you know what's going on like this:

'Application.DisplayAlerts = False
Windows.Activate("ALC60-2700.txt")
ActiveWorkbook.Close
'Application.DisplayAlerts = True

If all is well you can remove the ' so you will not be bugged with the "Do you want to save changes" or "You have not updated the text document to Excel" warning messages.

Rusty

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

 

[jewel]

hey thanks guys very much for all your help

ok - I missed the part about importing the data and was opening the txt file instead - on rerunning the macro and importing as suggested the txt file closed automatically. so thanks and I will be more observant next time. Little learning curve here!!

Now I have re-recorded the macro starting with clearing, importing and tidying columns - just working on
getting the new data into the 2nd sheet.

so will post back with specifics if need be

so once again thanks heaps
Dianne

 

[jewel]

ok hopefully last post

I now have a command button on my 2nd sheet - so all they need to do is press it and all works fine.

Is it a good idea to hide the 1st sheet?

thanks
Dianne