need macro help 2

[FacilitiesCAD]

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&quot.Select
ActiveSheet.Paste
Selection.End(xlDown).Select
Selection.End(xlDown).Select
End Sub

 

[gb433054]

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&quot.Cell(n,1) = L
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

 

[FacilitiesCAD]

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&quot.Cell(n, 1) = L
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&quot.Cell(n, 1) = L
'' this is the line that didn't work
Next n
'' completes the loop
End Sub

 

[FacilitiesCAD]

I've played with the file some more and I understand some of my mistakes. I changed
Workbook.Sheets("Sheet1&quot.Cell(n, 1) = L
to
cells(n,1) = L
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 <> &quot;&quot;. 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 <> &quot;&quot;
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Activate
Loop
End Sub


Thanks,

 

[gb433054]

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 = 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 = &quot;s:\cd-eng\draw_lst.txt&quot;


' 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(&quot;Sheet1&quot.Cells(n, 1) = L
'' this is the line that didn't work
Next n
'' completes the loop
End Sub

 

[FacilitiesCAD]

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

 

[gb433054]

Replace the line of text that reads

Input #1, L(i)

with

Line Input #1, L(i)

that should work ok

 

[FacilitiesCAD]

Thanks,
That makes the routine more helpful.

Tim

Ps I've given you a star for it.