New please help....??

[james912375]

Hi,

i am new VB and im trying upgrade an excel data compiling sheet.

i have written code to import 2 separate .txt files save them into hidden sheets and then and then move both of those sheets into one premade calculation sheets.
This works fine but i need to be able to change the files in the file path automatically so i can save new files in there and and when the macro is run the new values show up. This also seems to be hard beacuse i cant figure out how import these without a name on them so i have to input the original name into the code which obviously then wont allow me to change the file.. Please help!!!! i will highlight the parts of my code i think may need to be changed.. Thanks in advance!!

Dim master, master1 As Workbook

Set master = Workbooks.Open("C:\Documents and Settings\hodsonj\My Documents\txt files fwd\PARFWD.txt")
'i need the above to use any .txt files in the txt files fwd directory
counter = 3

Do Until ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).Value = ""

counter = counter + 1
Loop

mastercounter = 3

Do Until master.Sheets("PARFWD").Cells(mastercounter, 1).Value = "" 'i need the above reference the same as above & so on
ThisWorkbook.Sheets("Sheet1").Range(counter & ":" & counter).Value = master.Sheets("PARFWD").Range(mastercounter & ":" & mastercounter).Value

counter = counter + 1
mastercounter = mastercounter + 1

Loop


Set master1 = Workbooks.Open("C:\Documents and Settings\hodsonj\My Documents\txt files aft\PARAFT.txt")


counter = 3

Do Until ThisWorkbook.Sheets("Sheet2").Cells(counter, 1).Value = ""

counter = counter + 1
Loop

mastercounter = 3

Do Until master1.Sheets("PARAFT").Cells(mastercounter, 1).Value = ""
ThisWorkbook.Sheets("Sheet2").Range(counter & ":" & counter).Value = master1.Sheets("PARAFT").Range(mastercounter & ":" & mastercounter).Value

counter = counter + 1
mastercounter = mastercounter + 1

Loop

master.Close
master1.Close

Dim counter1 As Integer
counter1 = 3

Do Until ThisWorkbook.Sheets("Sheet1").Cells(counter1, 1).Value = ""
ThisWorkbook.Sheets("SINS Comp Raw Data").Range("A" & counter1, "O" & counter1).Value = ThisWorkbook.Sheets("Sheet1").Range("A" & counter1, "O" & counter1).Value
ThisWorkbook.Sheets("Sheet1").Range("A" & counter1, "O" & counter1).Value = ""
counter1 = counter1 + 1
Loop

Dim counter2 As Integer
counter2 = 3

Do Until ThisWorkbook.Sheets("Sheet2").Cells(counter2, 1).Value = ""
ThisWorkbook.Sheets("SINS Comp Raw Data").Range("Q" & counter2, "AE" & counter2).Value = ThisWorkbook.Sheets("Sheet2").Range("A" & counter2, "O" & counter2).Value
ThisWorkbook.Sheets("Sheet2").Range("A" & counter2, "O" & counter2).Value = ""
counter2 = counter2 + 1
Loop


End Sub

​

Thanks so much!!!

 

[sykes]

Hi, and welcome to Mr. Excel!

You could use "GetOpenFilename" for this, I think - so your users could select the file from the filebrowser, then you could use the string output in your code:

Dim master, master1 As Workbook

Dim str As String

str = Application.GetOpenFilename("Text files, *.txt")
If str = "False" Then msgbox "You need to select a file, in order to continue" : Exit Sub

Set master = Workbooks.Open(str)

'i need the above to use any .txt files in the txt files fwd directory
counter = 3

Do Until ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).Value = ""

counter = counter + 1
Loop

mastercounter = 3

Do Until master.Sheets("PARFWD").Cells(mastercounter, 1).Value = "" 'i need the above reference the same as above & so on
ThisWorkbook.Sheets("Sheet1").Range(counter & ":" & counter).Value = master.Sheets("PARFWD").Range(mastercounter & ":" & mastercounter).Value

You can tart it all up a bit, and of course repeat the exercise for your other files (master1), but this should set you on the right path (pardon the pun!!)
Incidentally - if you wanted the default location:
"C:\Documents and Settings\hodsonj\My Documents\txt files fwd\PARFWD.txt"
to be displayed when the file dialogbox is opened, you could change the directory and path earlier in your code:

ChDrive "c:\"
ChDir "C:\Documents and Settings\hodsonj\My Documents\txt files fwd\PARFWD.txt"

 

[james912375]

Hi, and welcome to Mr. Excel!

You could use "GetOpenFilename" for this, I think - so your users could select the file from the filebrowser, then you could use the string output in your code:

Dim master, master1 As Workbook
 
Dim str As String
 
str = Application.GetOpenFilename("Text files, *.txt")
If str = "False" Then msgbox "You need to select a file, in order to continue" : Exit Sub
 
Set master = Workbooks.Open(str)
 
'i need the above to use any .txt files in the txt files fwd directory
counter = 3
 
Do Until ThisWorkbook.Sheets("Sheet1").Cells(counter, 1).Value = ""
 
counter = counter + 1
Loop
 
mastercounter = 3
 
Do Until master.Sheets("PARFWD").Cells(mastercounter, 1).Value = "" 'i need the above reference the same as above & so on
ThisWorkbook.Sheets("Sheet1").Range(counter & ":" & counter).Value = master.Sheets("PARFWD").Range(mastercounter & ":" & mastercounter).Value

You can tart it all up a bit, and of course repeat the exercise for your other files (master1), but this should set you on the right path (pardon the pun!!)
Incidentally - if you wanted the default location:
"C:\Documents and Settings\hodsonj\My Documents\txt files fwd\PARFWD.txt"
to be displayed when the file dialogbox is opened, you could change the directory and path earlier in your code:

ChDrive "c:\"
ChDir "C:\Documents and Settings\hodsonj\My Documents\txt files fwd\PARFWD.txt"

Sykes, Thanks so much for the info!!
My only other issue would be with thuis statement-

[Do Until master.Sheets("PARFWD").Cells(mastercounter, 1).Value = "" 'i need the above reference the same as above & so on
ThisWorkbook.Sheets("Sheet1").Range(counter & ":" & counter).Value = master.Sheets("PARFWD").Range(mastercounter & ":" & mastercounter).Value]

How would i get the sheet reference("PARFWD"). to update with the new file path as it seems when excel imports the .txt file it automatically names the sheet which mean the above statement would not work.??

Thanks again so much for your help!!

Cheers

James

 

[james912375]

Sykes,
Thanks for the info i worked the rest out , your code worked perfect and i just changed it to [workbooks.activesheets] for the next section.
Thanks

James

 

[sykes]

Hi James
Sorry for the late reply - I was working all day yesterday, then drove most of the length of England, before falling in to a hotel room!

Glad the solution did most of what you needed, and that you figured the rest out.
I'm also glad that your first post turned out to be worthwhile! It really is a superb resource this isn't it?

All the best, and thanks for the feedback - it helps other folks using these posts as a reference, to know what the final solution was, and that it was successful.

 

[james912375]

Sykes,
Yeah its a great resource and i am very happy with the outcome!
I have one other question, what is the best way to say in code "the selected cell"
i.e. a macro is running that needs to save data into the cell that has been selected ?

I will post the finished code in here for others to use if they need it.

Cheers

james

 

[sykes]

Hi James

Sorry - really busy this week, and have only just got back to my hotel.

Suggest you use "ActiveCell." If you run this from a code module it should demonstrate the use and the syntax:

Sub test_active_cell()
Dim txt As String
txt = InputBox("Hi James. Please type your text for the active cell.........", "ACTIVE CELL TEXT TEST", "Type in here...")
ActiveCell.Value = txt
MsgBox "Your text " & """" & txt & """" & " has been added into cell " & ActiveCell.Address & " on worksheet " & Me.ActiveSheet.Name
End Sub

Hope this helps.

 

[sykes]

James
Only just noticed that when I tested the above code I used the .me keyword, so in order for it to work, you'll need to put it into the "This Workbook" code module in your VBA project.

The alternative is to replace the "Me" keyword with "ThisWorkbook" thus:

Sub test_active_cell()
Dim txt As String
txt = InputBox("Hi James. Please type your text for the active cell.........", "ACTIVE CELL TEXT TEST", "Type in here...")
ActiveCell.Value = txt
MsgBox "Your text " & """" & txt & """" & " has been added into cell " & ActiveCell.Address & " on worksheet: " & ThisWorkbook.ActiveSheet.Name
End Sub

...or even not use either, and just rely upon "ActiveSheet..."

Sub test_active_cell()
Dim txt As String
txt = InputBox("Hi James. Please type your text for the active cell.........", "ACTIVE CELL TEXT TEST", "Type in here...")
ActiveCell.Value = txt
MsgBox "Your text " & """" & txt & """" & " has been added into cell " & ActiveCell.Address & " on worksheet: " & ActiveSheet.Name
End Sub

...then you should be able to plonk the code anywhere in your VBA project.