Open Test file with VBA with a changing file number
 

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael

Open Test file with VBA with a changing file number
 

I assume that there is only one file in the directory that starts with
ni_wa_subinv_trans_ If that is the case you can use the Dir function similar
to you would in DOS.

Dir "C:\ni_wa_subinv_trans_*.txt"

this will return the full file name and you can then open the file...

HTH

"Michael from Austin" wrote:

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael

Open Test file with VBA with a changing file number
 

It worked. Thanks you so very much!!!

"Jim Thomlinson" wrote:

I assume that there is only one file in the directory that starts with
ni_wa_subinv_trans_ If that is the case you can use the Dir function similar
to you would in DOS.

Dir "C:\ni_wa_subinv_trans_*.txt"

this will return the full file name and you can then open the file...

HTH

"Michael from Austin" wrote:

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael

Open Test file with VBA with a changing file number
 

What if the file name is always preceeded by the date. How did I get a macro
to open up a file that is named the same all the time, but has the date at
the end always change?

--
Regards,
Michael


"Michael from Austin" wrote:

It worked. Thanks you so very much!!!

"Jim Thomlinson" wrote:

I assume that there is only one file in the directory that starts with
ni_wa_subinv_trans_ If that is the case you can use the Dir function similar
to you would in DOS.

Dir "C:\ni_wa_subinv_trans_*.txt"

this will return the full file name and you can then open the file...

HTH

"Michael from Austin" wrote:

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael

Open Test file with VBA with a changing file number
 

I assume that you want to find this file on a daily basis and will thus be
using the PC date (todays date) when you run the macro. You will ned to take
the PC date and change the format to the date format shown in the text file
name. Then you will insert this string into the title of the file name so the
macro will search/pick the file with todays date only.
(The date from my PC comes as 10/14/2005 in the example below)

First, at the beginning of your macro,
Dim V1 As String

(Create a new string to use as the variable for the date- I used V1 above)

V1 = Replace(FormatDateTime(Date, vbShortDate) , "/", "-")

(Gets PC date and replaces / with -)

V1 = Replace(V1, "2005", "05")

(Replaces 2005 with 05)

Then, in your macro where you have the file name you are looking for:
"J:\Filename_" & V1 & ".txt"

(Manually replace " & V1 & " where the date resides in the file name to
reference the newly formatted date--include the colons. This inserts the new
date string into the name of the file that you want the macro to get.)

The example output filename would be "J:\Filename_10-14-05.txt"

"Michael from Austin" wrote:

What if the file name is always preceeded by the date. How did I get a macro
to open up a file that is named the same all the time, but has the date at
the end always change?

--
Regards,
Michael


"Michael from Austin" wrote:

It worked. Thanks you so very much!!!

"Jim Thomlinson" wrote:

I assume that there is only one file in the directory that starts with
ni_wa_subinv_trans_ If that is the case you can use the Dir function similar
to you would in DOS.

Dir "C:\ni_wa_subinv_trans_*.txt"

this will return the full file name and you can then open the file...

HTH

"Michael from Austin" wrote:

I am trying to get VBA to open a text file called ni_wa_subinv_trans_XXXXXXX
and format in the Import wizard. The "ni_wa_subinv_trans_" will always be the
same , the "XXXXXX" will always be different. What can I do?
--
Regards,
Michael