Transfer File

rachmul18 · May 26th, 2011, 07:17 AM

Hi All,

I need to import a file that has trailing character that is not needed. For example, all states have the following file name Apples_STATE_Oranges_XX_YY_DD_FF where XX_YY_DD_FF change with each state where as APPLES_STATE_ORANGES stays constant however with state being dynamic.

Code:

 Location1 = Location & "Oranges.xls"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "cOranges", Location1, True

The problem is access can't find "oranges.xls" because in the destination folder its oranges_xx_yy_zz_dd.xls"

Any help would be greatly appreciated

JP · May 26th, 2011, 10:56 AM

Are there other files in the folder?

You could use the Dir Function to grab the filename of each file in the folder, then compare Left$(filename, 7) with "Oranges" to see if it matches.

gbianchi · May 27th, 2011, 08:43 AM

Hi there..

The dir command is a nice solution, but you can also do something like DIR("oranges*), to get all the files that match the start with oranges...

HiTechCoach · May 27th, 2011, 01:24 PM

The DIR() does use wildcards similar to how Jet/ACE (Access) SQL does with t he LIKE.

If you want all XLS files starting with orange then use:

Dir("\\path\folder\orange*.xls")

Check the help to see how to use DIR() to find all the files in a folder that match your search..

rachmul18 · May 28th, 2011, 07:24 PM

Quote:

Originally Posted by JP

Are there other files in the folder?

You could use the Dir Function to grab the filename of each file in the folder, then compare Left$(filename, 7) with "Oranges" to see if it matches.

Thanks! I have multiple files in the folder ....for example

xx_yy_STATE_Oranges_Blah

xx_yy_STATE_apples_Blah

xx_yy_STATE_pears_BLAH

I'm using a form to so if I showed the file path to say "xx_yy_me.state.value &"_" Dir(oranges*) ...would that work?

Thanks for your help!

HiTechCoach · May 29th, 2011, 12:09 AM

<<I'm using a form to so if I showed the file path to say "xx_yy_me.state.value &"_" Dir(oranges*) ...would that work?>>

No.

Did you check the help for the DIR() function?

You will need or the full path as the parameter for the DIR().

On your form are you wanting to list all the files in a folder that match your search string? If yes the you will have to use some VBA code to read all the file names and popular some type of control. You could use a List box, Combo box, or tree view control.

This may help:

http://www.everythingaccess.com/tuto...es-recursively

rachmul18 · June 7th, 2011, 01:18 PM

Thank you all for your help!

Code:

 location = f1
   location = f1 & me.state.value

Location1 = Dir(Location & "oranges*.xls")

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "cOranges", Location1, True

Thank you! Thank you! Thank you!

HiTechCoach · June 7th, 2011, 06:59 PM

You're welcome.

Glad we could assist.