Logo 
Search:

MS Office Forum

Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Another Newbie question

  Asked By: Koila    Date: Dec 17    Category: MS Office    Views: 873
  

I have two Excel Macro/VB question.

The first sounds rather simple. There is already a VB script written
that prompts the user to open a text file and subsequently gathers
information from that file. What i'd like to do is automate the
process such that the script goes into a folder and pulls all of the
text files one by one while gathering the information (ie. no
subsequent prompts).

Here's what the code looks like now (note this was written by someone
very familiar with VB who no longer works with us).

*********************************************************************
Sub Read_Specimen_data()

' This subroutine will read in the specimen Label, Width, Thickness,
and Max Load
' from a file and place them into columns of the worksheet.

Dim line1 As String * 80
Dim label As String
Dim filename As String
Dim layupcode As String
Dim comment As String
Dim testdate As String

Dim nrow As Integer
Dim nspec As Integer

Dim width As Double
Dim thickness As Double
Dim maxload As Double

Dim ws1 As Worksheet

Set ws1 = Worksheets("Data")

' Turn off screen updating to speed up the process

Application.ScreenUpdating = False

nrow = Application.WorksheetFunction.CountA(ws1.Range("C:C")) + 1

' Get the file name from the user

filename = Application.GetOpenFilename
Open filename For Input As #1

' Read a line from the input file

Do While Not EOF(1) ' Check for end of file

Input #1, line1

If (Mid(line1, 1, 9) = "Sample ID") Then

testdate = Mid(line1, 49, 13)

End If


If (Mid(line1, 1, 19) = "Number of specimens") Then

nspec = Mid(line1, 21, 6)

End If

If (Mid(line1, 1, 6) = "1:[LAY") Then

layupcode = Mid(line1, 26, 40)

End If

If (Mid(line1, 1, 6) = "2:[COM") Then

comment = Mid(line1, 26, 40)

End If

If (Mid(line1, 1, 5) = "Width") Then

width = Mid(line1, 19, 6)
ws1.Cells(nrow, 4).Value = width

End If

If (Mid(line1, 1, 9) = "Thickness") Then

thickness = Mid(line1, 19, 6)
ws1.Cells(nrow, 5).Value = thickness

End If

If (Mid(line1, 1, 14) = "Specimen label") Then

label = Mid(line1, 18, 10)
ws1.Cells(nrow, 3).Value = label

End If

If (Mid(line1, 1, 18) = "Maximum Load point") Then

maxload = Mid(line1, 53, 10)
ws1.Cells(nrow, 6).Value = maxload
nrow = nrow + 1

End If

Loop

Close #1

ws1.Cells(nrow - nspec, 1).Value = filename
ws1.Cells(nrow - nspec, 2).Value = testdate
ws1.Cells(nrow - nspec, 7).Value = layupcode
ws1.Cells(nrow - nspec, 8).Value = comment

Range(Cells(nrow - nspec, 1), Cells(nrow - 1, 1)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 2), Cells(nrow - 1, 2)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 7), Cells(nrow - 1, 7)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

Range(Cells(nrow - nspec, 8), Cells(nrow - 1, 8)).Select
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = True
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With

End Sub
*********************************************************************


The second issue is more complex.

I have created a macro that I'd like to repeat in multiple cells, but
the trick is that the range selected by the macro changes everytime,
and it also changes worksheets.

So in lamens terms, here's what the macro does:

copy info from cells. (in parent worksheet)
paste into cells below.
select cell (in which information is pasted) and highlight range.
Move to next worksheet and select a range.
and update information on the parent (data) worksheet.

What I'd like it to do is to repeat this action (copy and paste into
other cells) while selecting a different range in a different
worksheet.

The macro looks like this:

*******************************************************************
Range("S2:U2").Select
Selection.Copy
Range("S5").Select
ActiveSheet.Paste
Range("S5").Select
Sheets("017049RA04").Select
Range("D99").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "=RC[-2]-R99C2"
Range("E99").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("F99").Select
ActiveCell.FormulaR1C1 = "=RC[-2]"
Range("D99:F99").Select
Selection.Copy
Range("D100:F452").Select
ActiveSheet.Paste
Range("F103").Select
Application.CutCopyMode = False
Sheets("Data").Select
Range("S5").Select
ActiveCell.FormulaR1C1 = "=VLOOKUP(0.004,Data!R[94]C[-15]:R[447]C
[-13],2,TRUE)"
Range("S5").Select
Sheets("017049RA04").Select
Sheets("017049RA04").Name = "017049RA04"
Sheets("Data").Select
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(0.004,'017049RA04'!R[94]C[-15]:R[447]C[-13],2,TRUE)"
Range("S5").Select
Selection.Copy
Range("T5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=VLOOKUP(RC[-1],'017049RA04'!R[94]C[-15]:R[447]C[-
13],2,TRUE)"
Range("T5").Select
Selection.NumberFormat = "#,##0.0"
Selection.NumberFormat = "#,##0.00"
Selection.NumberFormat = "#,##0.000"
Selection.NumberFormat = "#,##0.0000"
Selection.NumberFormat = "#,##0.00000"
Selection.NumberFormat = "#,##0.000000"

*******************************************************************


Let me know if this makes sense and if you can point me to good
VB/Macro online tutorials.

Share: 

 

2 Answers Found

 
Answer #1    Answered By: Kristin Johnston     Answered On: Dec 17

Unfortunately no time to help with your code  today. For online courses,
do a Google on "vba excel  online tutorial". You will find many options
available to you, of which some are free of charge as well.

Speaking of Google and searching, Google is great for finding example
code, and the Google Usenet search works  great in *excel* newsgroups.
Lastly, your first question  came up about a week/two weeks ago in this
forum. Try searching the posts for the last month or so, and you will
find discussions about accessing the FSO and opening files, etc.

 
Answer #2    Answered By: Beatriz Silva     Answered On: Dec 17


For ur first question  u can do one thing, u can store all the required file
names in one array variable and then can rotate the loop and perform required
operations. Also another option is that you can directly retrieve all the
required files  from the required folder  as following.

For each files in System.io.directory.getfiles("C:\Data")
'''in this loop perform all required operations for given
file.''''''''''
Although above code  in VB.NET for VB u can do the same thing. But for VB i don't
remember particular function for retrieving files, I think u must be knowing.


And for ur second question, I am not getting what u want. Then also i m sending
u reply wht i m getting from ur question.
For ur second question's solution, u can rotate a loop and can perform the same
operation. Initally just u need to fix one criteria.

 
Didn't find what you were looking for? Find more on Another Newbie question Or get search suggestion and latest updates.




Tagged: