chris-red Have you considered a TDM?
Joined: 21 Sep 2005 Karma :
|
Posted: 12:58 - 14 Mar 2019 Post subject: Excel VB Help |
|
|
Hi all I've never touched this before, I have tried watching/reading a few tutorials and I haven't managed to get my head round it. I want to output the result to a text file
Here is the whole Script
Code: | Sub User_Creation_Ldf()
Dim Last_Row As Long
Dim Row_counter As Integer
Dim Col_counter As Integer
Application.ScreenUpdating = False
Sheets("Final").Activate
Range(Range("B1"), Range("B65000")).ClearContents
Sheets("User_List").Activate
Last_Row = Cells(Rows.Count, 1).End(xlUp).Row
For Row_counter = 1 To Last_Row
Sheets("Template").Activate
Range(Range("A1"), Range("A2000")).ClearContents
Sheets("User_List").Activate
Cells(Row_counter, 1).Copy
Sheets("Template").Range("A1").PasteSpecial xlPasteValues
Cells(Row_counter, 2).Copy
Sheets("Template").Range("A2").PasteSpecial xlPasteValues
Cells(Row_counter, 3).Copy
Sheets("Template").Range("A3").PasteSpecial xlPasteValues
Cells(Row_counter, 4).Copy
Sheets("Template").Range("A4").PasteSpecial xlPasteValues
Call Create_Final
Call CreateTXT
Next Row_counter
End Sub
Public Sub Create_Final()
Dim LastRow As Long
Dim Copy_Counter As Integer
Copy_Counter = 30
Sheets("Template").Activate
Range(Range("C1"), Range("C" & Copy_Counter)).Copy
Sheets("Final").Activate
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
If LastRow = 1 Then
LastRow = LastRow + 1
Else
LastRow = LastRow + 2
End If
Cells(LastRow, 2).PasteSpecial xlPasteValues
End Sub
Sub CreateTXT()
Dim LastRow, FileNumber As Integer
Filename = "C:\folder\user.txt"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Final").Activate
FileNumber = FreeFile
[b]Open Filename For Output As FileNumber[/b]
For i = 1 To LastRow
sLine = sLine & Cells(i, B).Value
Print #FileNumber, sLine
sLine = ""
Next i
Close #FileNumber
MsgBox "Script Created"
End Sub
|
This is what I have at the moment.
I have writen the CreateTXT part, TBH I have no idea what I'm doing.
The original script (everything without CreateTXT.)
Would read variables on a sheet, and use them to generate scripts and copy that to a different sheet from which a user would copy it to a txt file. I want to create this file when the button is pressed. Output is 1 column.
Debug is kicking off about the bold line. Can anyone help and explain it to me please?
Thanks,
Chris ____________________ Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything. |
|
The Shaggy D.A. Super Spammer
Joined: 12 Sep 2008 Karma :
|
Posted: 15:47 - 14 Mar 2019 Post subject: |
|
|
[edoit] Scrap that, FreeFile is a function, not a variable ____________________ Chances are quite high you are not in my Monkeysphere, and I don't care about you. Don't take it personally.
Currently : Royal Enfield 350 Meteor
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom > Royal Enfield Classic 500 Despatch Limited Edition (28 of 200) & CB Two-Fifty Nighthawk > ER5 |
|
Hawkeye1250FA World Chat Champion
Joined: 14 Oct 2016 Karma :
|
Posted: 14:36 - 18 Mar 2019 Post subject: |
|
|
Does it need dynamic file numbering?
If not then simplify it by changing the ".. for output as file number"
To
"... For output as #1"
Then this line :
"PRINT #filenumber, SlIne"
Changes to
"WRITE #1,SLINE"
and
"Close #filenumber"
Changes to
"Close #1"
Edit: #bluepeter
Code: |
Sub CreateTXT()
Dim LastRow, FileNumber As Integer
Filename = "C:\folder\user.txt"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Final").Activate
Open Filename For Output As #1
For i = 1 To LastRow
sLine = sLine & Cells(i, 2).Value
Write #1, sLine
sLine = ""
Next i
Close #1
MsgBox "Script Created"
End Sub
|
____________________ 1993 RF400R - 2002 Bandit 1200 - 2012 1250 FA |
|
chris-red Have you considered a TDM?
Joined: 21 Sep 2005 Karma :
|
Posted: 11:30 - 19 Mar 2019 Post subject: |
|
|
Hawkeye1250FA wrote: | Does it need dynamic file numbering?
If not then simplify it by changing the ".. for output as file number"
To
"... For output as #1"
Then this line :
"PRINT #filenumber, SlIne"
Changes to
"WRITE #1,SLINE"
and
"Close #filenumber"
Changes to
"Close #1"
Edit: #bluepeter
Code: |
Sub CreateTXT()
Dim LastRow, FileNumber As Integer
Filename = "C:\folder\user.txt"
LastRow = Cells(Rows.Count, 2).End(xlUp).Row
Sheets("Final").Activate
Open Filename For Output As #1
For i = 1 To LastRow
sLine = sLine & Cells(i, 2).Value
Write #1, sLine
sLine = ""
Next i
Close #1
MsgBox "Script Created"
End Sub
|
|
Wicked works great thanks. ____________________ Well, you know what they say. If you want to save the world, you have to push a few old ladies down the stairs.
Skudd:- Perhaps she just thinks you are a window licker and is being nice just in case she becomes another Jill Dando.
WANTED:- Fujinon (Fuji) M42 (Screw on) lenses, let me know if you have anything. |
|