Auto select file name txt file import - need help!!!

[ake]

Hi,

I'm on Excel 2007 first of all. I have a tab linked to import a txt file from a network location. Upon refreshing the data I get an "Import Text File" window at that network path for me to select the txt file I want to import. Is there a way to automate this? When my macro runs to this point in requires a user to manuall selectr a file. The file name is in sequence from day to day for example Account05252011.txt, Account05262011.txt and so on.

Thank you.
Ake

 

[doublej_jj]

I am not fantastic at Visual Basic, but I dabble. Seeing that your files are dated, my major question is:

Is there always the same relationship between what day it is and the date on the import file? For example, do you import the Account05262011.txt on the 27th?

If this is the case, somewhere in your spreadsheet, have a place to define the file name. If the above case is true:

A1=now()
A2="Account"&if(Month(a1)<10,"0","")&Month(a1)&if(Day(a1)<10,"0","")&Month(a1)&Year(a1)&".txt"

A2 should now look like your file name for yesterday (Note, the if statements are to pad the 0 in the month and the day.

Now in your visual basic code you can define your variable "File_Name" and set it equal to the value in cell A2. Below works, but there may be a more eloquent way

Dim File_Name As String
Range("A2").Select
File_Name = Selection.Value

Further, in your code now where it asks the user to input the file to be import, simply use the varialbe File_Name.

Hope if that isn't your exact solution, that it may help you get there. They say it is not the destination, but the journey to get there that is important.

Jeff

 

[ake]

Hi,

Thanks Jeff, that does put on the journey to getting this figured out but I've not used the Dim function before so if you or anyone that knows could help show me how I would apply it to my macro below?

Thank you again for your help.
Ake



Sub Step1_UpdateAccountData()
'
' Step1_UpdateAccountData Macro
'
'
Sheets("Account Data Import").Select
Application.CutCopyMode = False
Range("A3:H65536").Select
Selection.ClearContents
Range("I2").Select
Selection.QueryTable.Refresh BackgroundQuery:=False
Range("A2:H2").Select
Selection.AutoFill Destination:=Range("A2:H" & Range("I65536").End(xlUp).Row)
Range("I1").Select
Sheets("REFRESH").Select
Range("B3").Select
End Sub