Import Text File w/ VBA
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 |
Import Text File w/ VBA
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 |
Import Text File w/ VBA
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 |
Import Text File w/ VBA
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 |
Import Text File w/ VBA
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 |
All times are GMT +1. The time now is 05:08 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com