×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

need macro help
2

need macro help

need macro help

(OP)


Sub Macro1()
'
I've found alot of hints from reading these pages and I'm now trying to pull them together.  I want to make an access database for all my autocad drawings on a shared drive.  unfortunately I'm a little unfamiliar with alot of the process.  The way i see to do it I have a few steps.

1) create and run a batch file (.bat) to make a text file of that lists all the drawings. The bat file is one line:

dir *.dwg /s >draw_lst.txt

the /s switch goes into all the subdirectories and the *.dwg gets only the autocad drawings

2)open the file draw_lst.txt into excell
I actually do this twice and save the excel file under 2 differnt names. (once to get the directory information and a second time to get file specific information like the date last saved and file name)

3) now I need to get the directory information connected with the file name.  I tried to use record a macro and generic functions of end down to select everything needed and it didn't treat it just right. bellow is the macro.

Ideally it should go to the next row there is text in copy it then paste it in the next few rows there was text in unfortunately it copied the range as the actual range a7:a17 instead of the next group down.

there is one more problem to solve after this involving empty rows.   Any Ideas appreciated

Thanks,


' Macro1 Macro
' Macro recorded 7/23/03 by Administrator
'

'
    Selection.End(xlDown).Select
    Selection.Copy
    Selection.End(xlDown).Select
    Range("A7:A17").Select
    ActiveSheet.Paste
    Selection.End(xlDown).Select
    Selection.End(xlDown).Select
End Sub

RE: need macro help

2
If you are creating you database in excel then this is how I would approach it.

Sub GetFileList()

' Create array for each line in file
Dim L() as string
' Create Variable to hold line number
Dim i as Integer
i=0

Dim FileList as string

FileList = "My FileList Path, EG C:\Temp\MyFileList.txt"
Open FileList for Input as #1
   Do while not eof(1)
   i=i+1
   ' Redimension array size for each element
   Redim Preserve L(i) as string
   ' Assign line value to Array element
   Input #1, L(i)
   Loop
Close #1

'-----------------------------------------

'Enter contents of Array into Column A
For n = 1 to i
   ' n = row number
   workbook.sheets("Sheet1").Cell(n,1) = L(n)
Next n

End Sub

Draw a button on a sheet from the Forms tool bar then right click and choose Assign Macro, Select the macro name.
Click the button and the contents of your file should be column A of sheet1 and rows 1 to n

Hope this helps

Dav

RE: need macro help

(OP)
I tried your program and its a little over my head.  I got the runtime error '424' Object required when I tried to run it.  It failed on:
   Workbook.Sheets("Sheet1").Cell(n, 1) = L(n)
I'm going to add coment lines with '' to the file saying what I believe the code is doing.  Please let me know if I'm right.

Sub GetFileList()
'' Defines the macros name
' Create array for each line in file
Dim L() As String
'' defines L() as a text
' Create Variable to hold line number
Dim i As Integer
'' defines i as a number/integer
i = 0
'' sets i to 0 to start counting
Dim FileList As String
'' defines FileList as a text
FileList = "s:\cd-eng\draw_lst.txt"
'' Only line of code I changed to be my file specific
Open FileList For Input As #1
'' here is where I lose you. Open the file ?for what?
   Do While Not EOF(1)
'' a do while loop that goes until the end of file
   i = i + 1
''  just a step incriment
   ' Redimension array size for each element
   ReDim Preserve L(i) As String
'' somehow L(#) is an array and a string variable
   ' Assign line value to Array element
   Input #1, L(i)
'' L(i) becomes the text of line i
   Loop
'' ends your do loop
Close #1
'' I would expect close filelist
'-----------------------------------------

'Enter contents of Array into Column A
For n = 1 To i
'' another loop to put L(#) into each cell
   ' n = row number
   Workbook.Sheets("Sheet1").Cell(n, 1) = L(n)
'' this is the line that didn't work
Next n
'' completes the loop
End Sub

RE: need macro help

(OP)
I've played with the file some more and I understand some of my mistakes.  I changed
Workbook.Sheets("Sheet1").Cell(n, 1) = L(n)
to
cells(n,1) = L(n)
and it worked for the most part except the file sizes were greater 1,000 so the coma forced it to break lines in two.  I was able to solve my macro problem with a do while loop involving <> "".  I am attaching it bellow to see if it is helpfull to anyone else.

'
' Macro7 Macro
' Macro recorded 7/23/2003 by Timothy Healey
'
'
Sub Macro7()
    Selection.End(xlDown).Select
    Selection.Copy
    Selection.End(xlDown).Select
      Do While ActiveCell.Value <> ""
            ActiveSheet.Paste
            ActiveCell.Offset(1, 0).Activate
        Loop
End Sub


Thanks,

RE: need macro help

Sorry about the mistake in the previous example, however you find this works. Copy and paste it into your module

Sub GetFileList()
' Defines the macros name
' Create array for each line in file
' eg. L(1) = line 1 of file
'     L(2) = line 2 of file
'     L(n) = Line n of file
Dim L() As String

' Create Variable to hold line number
Dim i As Integer

' defines i as a number/integer
i = 0

' defines FileList as a text string
' to contain file path
Dim FileList As String


' Assign file path to variable,
FileList = "s:\cd-eng\draw_lst.txt"


' Open the file path declared above to read (for Input)
' As #1 is just a file description
Open FileList For Input As #1

   ' Loop through each line of the file
   ' until the end, the (1) is the file
   ' descriptor mentioned above
   Do While Not EOF(1)
   ' increment the counter
   i = i + 1
   ' Redimension array size for each element
   ' EG of first loop L() will have 1 element
   ' on second loop it will have 2 elements etc
   ReDim Preserve L(i) As String
   

   ' Input means read the current line of file 1
   ' and assign the value of the string to L(i)
   Input #1, L(i)
   
   ' do next loop if not end of file
   Loop

' I would expect close filelist
Close #1

'-----------------------------------------

'Enter contents of Array into Column A

' Loop the number of lines found in FileList
For n = 1 To i
' another loop to put L(#) into each cell
   ' n = row number
   ' Sorry this line was slightly wrong
   ThisWorkbook.Sheets("Sheet1").Cells(n, 1) = L(n)
'' this is the line that didn't work
Next n
'' completes the loop
End Sub

RE: need macro help

(OP)
Dav,
 I've run your routine and it works fine with the exception that it breaks most of my lines in two because I have a comma in the file size portion of the directory list.  I'm wondering why you would do it this way instead of using the file open option.  I'm guessing this is because you expect to be oppening the file a number of times and then the user selections become a little tedious.

Tim

RE: need macro help

Replace the line of text that reads

Input #1, L(i)

with

Line Input #1, L(i)

that should work ok

RE: need macro help

(OP)
Thanks,
  That makes the routine more helpful.

Tim

Ps I've given you a star for it.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources