P
Phil Rutter
hello All,
We have a list of files that i would like to open one at a
time read the data and export it to a excel spread sheet
then it should close file open the next file and apend
this also to the same spread sheet.
my code at the moment will only open one file and export
its contents to the excell spread sheet.
please see below
Sub Listfiles()
Open "F:\ACI-03-0760.DOC" For Input As #1
r = 1
Cells(r, 1) = "Quote Name"
Cells(r, 2) = "To"
Cells(r, 3) = "Contact"
Cells(r, 4) = "From"
Cells(r, 5) = "Fax"
Cells(r, 6) = "Date"
Cells(r, 7) = "sales Rep"
Range("a1:z1").Font.Bold = True
r = r + 1
Cells(r, 2) = (Documents(1).Tables(1).Rows(1).Cells
(2)) 'customer name
Cells(r, 3) = (Documents(1).Tables(1).Rows(4).Cells
(2)) 'customer contact
Cells(r, 4) = (Documents(1).Tables(1).Rows(1).Cells
(4)) 'from
Cells(r, 5) = (Documents(1).Tables(1).Rows(2).Cells
(2)) 'fax number
Cells(r, 6) = (Documents(1).Tables(1).Rows(3).Cells(4)) '
date
Cells(r, 7) = (Documents(1).Tables(1).Rows(4).Cells(4)) '
Sales rep
Cells(r + 2, 2) = (Documents(1).Tables(2).Rows(2).Cells
(1)) ' Item Number
Cells(r + 2, 3) = (Documents(1).Tables(2).Rows(2).Cells
(2)) ' Part number
Cells(r + 2, 4) = (Documents(1).Tables(2).Rows(2).Cells
(3)) ' Description
Cells(r + 2, 5) = (Documents(1).Tables(2).Rows(2).Cells
(4)) ' Qty
Cells(r + 2, 6) = (Documents(1).Tables(2).Rows(2).Cells
(5)) ' Price
End Sub
We have a list of files that i would like to open one at a
time read the data and export it to a excel spread sheet
then it should close file open the next file and apend
this also to the same spread sheet.
my code at the moment will only open one file and export
its contents to the excell spread sheet.
please see below
Sub Listfiles()
Open "F:\ACI-03-0760.DOC" For Input As #1
r = 1
Cells(r, 1) = "Quote Name"
Cells(r, 2) = "To"
Cells(r, 3) = "Contact"
Cells(r, 4) = "From"
Cells(r, 5) = "Fax"
Cells(r, 6) = "Date"
Cells(r, 7) = "sales Rep"
Range("a1:z1").Font.Bold = True
r = r + 1
Cells(r, 2) = (Documents(1).Tables(1).Rows(1).Cells
(2)) 'customer name
Cells(r, 3) = (Documents(1).Tables(1).Rows(4).Cells
(2)) 'customer contact
Cells(r, 4) = (Documents(1).Tables(1).Rows(1).Cells
(4)) 'from
Cells(r, 5) = (Documents(1).Tables(1).Rows(2).Cells
(2)) 'fax number
Cells(r, 6) = (Documents(1).Tables(1).Rows(3).Cells(4)) '
date
Cells(r, 7) = (Documents(1).Tables(1).Rows(4).Cells(4)) '
Sales rep
Cells(r + 2, 2) = (Documents(1).Tables(2).Rows(2).Cells
(1)) ' Item Number
Cells(r + 2, 3) = (Documents(1).Tables(2).Rows(2).Cells
(2)) ' Part number
Cells(r + 2, 4) = (Documents(1).Tables(2).Rows(2).Cells
(3)) ' Description
Cells(r + 2, 5) = (Documents(1).Tables(2).Rows(2).Cells
(4)) ' Qty
Cells(r + 2, 6) = (Documents(1).Tables(2).Rows(2).Cells
(5)) ' Price
End Sub