Macro to open another file

[Francis Hookham]

A template, ADR.xlt, produces a single sheet workbook named ADR1.xls which
has macros which formats data imported from a file named
AgedDebtorReport.csv

So far an report file has to be opened, data selected ('Select all') and
copied, then the ADR1.xls activated and the data pasted in

It is not difficult to record and refine a macro to do this with the file
path included, BUT the template will be moved to another machine and a
different AgedDebtorReport.csv comes in each day, albeit with identical name

On the basis that both ADR1.xls and AgedDebtorReport.csv will alwsys be in
the same folder I have tried cutting down

"Workbooks.Open Filename:= _
"D:\Documents and Settings\Francis\`Tyler\AgedDebtorReport.csv"
to
Workbooks.Open Filename:="AgedDebtorReport.csv"

which seems to work until I start or change the report for a new one

Any suggestions please

Francis Hookham

 

[geo@OZ]

Change the line

Workbooks.Open Filename:= _
"D:\Documents and Settings\Francis\`Tyler\AgedDebtorReport.csv"

to

Workbooks.Open (ThisWorkbook.Path & "\AgedDebtorReport.csv")

This SHOULD then open the file Aged... in the same folder as the open
file you are running the code in. I am not sure how it will handle a
..csv file, presumably if you have had the code running OK with hard
coded file path it will work this way as well.

I have just spent several hours trying to find a solution for this
problem myself, and the above solution seems to be working for me as I
copy my two files to different folders and drives.

 

[Francis Hookham]

Thanks Mark - but I had a tough time until I realised the macro had to be in
an .xls spreadsheet rather than an .xlt template - I simply did not realise
that the template produced a .xls sheet which was floating around and not in
the same folder - this screwed everything up because it did not know where
AgedDebtorReport.csv was



I does not of course matter using an .xls to hold the macros because, as
soon as the data was imported, the sheet was saved in My Documents at today's
date, yymmdd ADR.xls



This is the subroutine which did the work - many thanks - Francis Hookham



Sub GetADRData()

TemporaryFileName = ThisWorkbook.Name

Workbooks.Open (ThisWorkbook.Path & "\AgedDebtorReport.csv")

LastRow = Cells(Rows.Count, 1).End(xlUp).Row

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column

Range(Cells(1, 1), Cells(LastRow, LastCol)).Copy

Windows(TemporaryFileName).Activate

Cells(1, 1).Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _

:=False, Transpose:=False

Application.DisplayAlerts = False

Windows("AgedDebtorReport.csv").Close

Application.DisplayAlerts = True

End Sub