I am trying to set up a macro to import a specific text file based on the
information the user puts into specific cells. They imput the company,
division and date and this generates a path & filename in a cell in the
spreadsheet. How can I get that path & filename into the macro? I've tried
quite a few options but with no success.

Thanks,
--
Suz

I'd do this in two steps. First, record the opening of a specific file.
Then edit the resulting VBA code, replacing the Filename:= parameter to
incorporate the variables provided by the user. You'll probably use the &
operator to combine some fixed components with some variables along with the
format operator to turn the input date into the appropriate file name,
something like
Filename := "S:\" & CompanyName & "\" & DivisionName & "\" &
text(Date,"mmddyy") & ".txt"
Along the way you can also use the immediate window in the VB Editor; type
the work print followed by the right-hand side of the equation above to see
that you get the expected path and filename.
--Bruce

"Suzseb" wrote:

I am trying to set up a macro to import a specific text file based on the
information the user puts into specific cells. They imput the company,
division and date and this generates a path & filename in a cell in the
spreadsheet. How can I get that path & filename into the macro? I've tried
quite a few options but with no success.

Thanks,
--
Suz

If you just want to open the text file you should use the following command
in your VBA:

Dim sCompany As String
Dim sDivision As String
Dim sDate As String
Dim sFilename As String

sCompany = Range("A1")
sDivision = Range("A2")
sDate = Replace(Range("A3"), "/", "")
sFilename = "C:\" & sCompany & "\" & sDivision & "\" & sDate & ".txt"


Workbooks.OpenText Filename:=sFilename

In the above example you should replace C:\ with the starting point of your
folder structure. You'll notice Replace(Range("A3"), "/", "") this ensures
that if the date is entered dd/mm/yyyy or mm/dd/yyyy ie. with slashes, which
are invalid for filenames, then they are removed.

I hope this helps.

--
Kurt Farrar
..NET Developer & Computer Enthusiast



"Suzseb" wrote:

I am trying to set up a macro to import a specific text file based on the
information the user puts into specific cells. They imput the company,
division and date and this generates a path & filename in a cell in the
spreadsheet. How can I get that path & filename into the macro? I've tried
quite a few options but with no success.

Thanks,
--
Suz

Thank you. I'll try that.
--
Suz


"Kurt Farrar" wrote:

If you just want to open the text file you should use the following command
in your VBA:

Dim sCompany As String
Dim sDivision As String
Dim sDate As String
Dim sFilename As String

sCompany = Range("A1")
sDivision = Range("A2")
sDate = Replace(Range("A3"), "/", "")
sFilename = "C:\" & sCompany & "\" & sDivision & "\" & sDate & ".txt"


Workbooks.OpenText Filename:=sFilename

In the above example you should replace C:\ with the starting point of your
folder structure. You'll notice Replace(Range("A3"), "/", "") this ensures
that if the date is entered dd/mm/yyyy or mm/dd/yyyy ie. with slashes, which
are invalid for filenames, then they are removed.

I hope this helps.

--
Kurt Farrar
.NET Developer & Computer Enthusiast



"Suzseb" wrote:

I am trying to set up a macro to import a specific text file based on the
information the user puts into specific cells. They imput the company,
division and date and this generates a path & filename in a cell in the
spreadsheet. How can I get that path & filename into the macro? I've tried
quite a few options but with no success.

Thanks,
--
Suz

Thank you. I'll let you know how it goes.
--
Suz


"bpeltzer" wrote:

I'd do this in two steps. First, record the opening of a specific file.
Then edit the resulting VBA code, replacing the Filename:= parameter to
incorporate the variables provided by the user. You'll probably use the &
operator to combine some fixed components with some variables along with the
format operator to turn the input date into the appropriate file name,
something like
Filename := "S:\" & CompanyName & "\" & DivisionName & "\" &
text(Date,"mmddyy") & ".txt"
Along the way you can also use the immediate window in the VB Editor; type
the work print followed by the right-hand side of the equation above to see
that you get the expected path and filename.
--Bruce

"Suzseb" wrote:

I am trying to set up a macro to import a specific text file based on the
information the user puts into specific cells. They imput the company,
division and date and this generates a path & filename in a cell in the
spreadsheet. How can I get that path & filename into the macro? I've tried
quite a few options but with no success.

Thanks,
--
Suz