Resend my activation email : Register : Log in 
BCF: Bike Chat Forums


Excel VB Help

Reply to topic
Bike Chat Forums Index -> The Geek Zone
View previous topic : View next topic  
Author Message

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :

PostPosted: 12:58 - 14 Mar 2019    Post subject: Excel VB Help Reply with quote

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. Laughing

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.
 Back to top
View user's profile Send private message You must be logged in to rate posts

The Shaggy D.A.
Super Spammer



Joined: 12 Sep 2008
Karma :

PostPosted: 15:47 - 14 Mar 2019    Post subject: Reply with quote

[edoit] Scrap that, FreeFile is a function, not a variable Smile
____________________
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 Classic 500 Despatch Limited Edition, 28 of 200
Previously : CB100N > CB250RS > XJ900F > GT550 > GPZ750R/1000RX > AJS M16 > R100RT > Bullet 500 > CB500 > LS650P > Bullet Electra X & YBR125 > Bullet 350 "Superstar" & YBR125 Custom
 Back to top
View user's profile Send private message You must be logged in to rate posts

Hawkeye1250FA
World Chat Champion



Joined: 14 Oct 2016
Karma :

PostPosted: 14:36 - 18 Mar 2019    Post subject: Reply with quote

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
 Back to top
View user's profile Send private message You must be logged in to rate posts

chris-red
Have you considered a TDM?



Joined: 21 Sep 2005
Karma :

PostPosted: 11:30 - 19 Mar 2019    Post subject: Reply with quote

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. Thumbs Up Thumbs Up
____________________
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.
 Back to top
View user's profile Send private message You must be logged in to rate posts
Display posts from previous:   

Post new topic   Reply to topic    Bike Chat Forums Index -> The Geek Zone All times are GMT + 1 Hour
Page 1 of 1

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Read the Terms of Use! - Powered by phpBB © phpBB Group
 

Debug Mode: ON - Server: discovery (www) - Page Generation Time: 2.68 Sec - Server Load: 3.69 - MySQL Queries: 16 - CDN Objects: 33 - Page Size: 48.13 Kb