Conditional import of Excel sheets

Hi, maybe someone can help me with the following: on a weekly basis I
need to import 3 Excel files into an existing empty table (I run a
delete query on this table before the import). The Excel files are
stored in the same folder every week. Each file has a field showing
[Current Year/Week No]. The three files show the current week for
this
year, previous year and the year before the previous year. How can
you
instruct MS Access (2003) to look up this field and to import only
the
approrpiate files (i.e. the files that refer to the reporting week)? I
do have
a calender function from which the user can select the date (and
deduct
the 2 previous dates). I should be able to incorporate that into the
code
(with a little help...).
Any ideas? Let me know if you need more info, thanks a million, Paul

0
Paul
10/24/2007 12:13:15 PM
access 16762 articles. 3 followers. Follow

9 Replies
1188 Views

Similar Articles

[PageSpeed] 9

Are there more than 3 files in the folder?  Is your problem finding the 
correct files among hundreds, or just reading the year/week value?  Or both?

"Paul" wrote:

> Hi, maybe someone can help me with the following: on a weekly basis I
> need to import 3 Excel files into an existing empty table (I run a
> delete query on this table before the import). The Excel files are
> stored in the same folder every week. Each file has a field showing
> [Current Year/Week No]. The three files show the current week for
> this
> year, previous year and the year before the previous year. How can
> you
> instruct MS Access (2003) to look up this field and to import only
> the
> approrpiate files (i.e. the files that refer to the reporting week)? I
> do have
> a calender function from which the user can select the date (and
> deduct
> the 2 previous dates). I should be able to incorporate that into the
> code
> (with a little help...).
> Any ideas? Let me know if you need more info, thanks a million, Paul
> 
> 
0
Utf
10/24/2007 1:40:02 PM
On Oct 24, 3:40 pm, Lance <La...@discussions.microsoft.com> wrote:
> Are there more than 3 files in the folder?  Is your problem finding the
> correct files among hundreds, or just reading the year/week value?  Or both?

Hi Lance, thanks for your reply. The folder will be filled with a
total of 8 files every week. They are all appended into separate
tables (apart from these 3). I intend to clean up this folder once
every while, so we're talking about maybe 50 files max.

0
Paul
10/25/2007 7:53:51 AM
OK try this out, you may need to add some references for ADO and microsoft 
scripting.  Also, it always treats the excel spreadsheet as having the first 
line as field names, regardless of what you put in.  It seems to add 1 to 
your cell reference line.  Sorry, I didn't have the time to figure that out.. 
but would love to know if you do.

I ran it through a group of 10 excel worksheets in a folder and looked for 
the word "ODD" in cell A2 ( entered in code as A1, see above for error ) and 
it worked well enough.  





Sub check_excel_files()
   Dim fsoSysObj As Scripting.FileSystemObject
   Dim fdrFolder As Scripting.Folder
   Dim filFile As Scripting.File
   Dim strPath As String
   
   strPath = "C:\Projects\LIBRARY"
   Set fsoSysObj = New Scripting.FileSystemObject
   Set fdrFolder = fsoSysObj.GetFolder(strPath)
   
   For Each filFile In fdrFolder.Files
      If filFile.Type = "Microsoft Excel Worksheet" Then
      
      '*****************************************************
      '* INSERT CODE TO CHECK EXCEL VALUE HERE
      '*****************************************************
         If x_value_from_excel(filFile.Path, "Sheet1", "A", 1, 0) = "ODD" Then
            MsgBox filFile.Path & " is an odd numbered file."
         End If
      '******************************************************
      
      End If
   Next filFile
   
   
   
   
   Set fsoSysObj = Nothing
   Set fdrFolder = Nothing
   
End Sub

Function x_value_from_excel(inFile As String, inWorkSheet As String, inCol 
As String, inRow As Integer, inFirstRowHasNames As Integer)
   
   On Error GoTo handler
   Dim cn As ADODB.Connection
   Dim rs As ADODB.Recordset
   
   Set cn = New ADODB.Connection
   Set rs = New ADODB.Recordset
   
   strquery = "SELECT * FROM [" & inWorkSheet & "$" & inCol & inRow & ":" & 
inCol & inRow + 1 & "]"
   
   With cn
      .Provider = "MSDASQL"
      .ConnectionString = "Driver={Microsoft Excel Driver (*.xls)};DBQ=" & 
inFile & "; ReadOnly=False;FirstRowHasNames=" & inFirstRowHasNames & ";"
      .Open
   End With
   
   Call rs.Open(strquery, cn)
   x_value_from_excel = rs(0)
   
  cn.Close
  Exit Function
handler:
  MsgBox Err.Description
  x_value_from_excel = "ERROR"
End Function
0
Utf
10/25/2007 2:02:01 PM
I have to say, Lance, this goes somewhat over my head...to put it
mildly. I do appreciate your time on this, though, but somehow I feel
this is getting too complicated (for me anyway). I have been thinking
this over (thanks to you and another expert) and I figured that the
files I do not need all have in common that they were downloaded on a
different date than the files I do need, which will always be
downloaded the same day. Could I use the date criteria somehow as a
condition to import or not? If possible, that seems easier to me.
Thanks again for your time. Paul

0
Paul
10/26/2007 1:50:28 PM
That's something I thought about putting in the script I posted before.  And 
it's certainly doable, but you'll still have to loop through files and 
extract the datecreated value from it.  But that would allow you to skip 
reading the excel file.

But let's step back from that for a second, maybe there's an easier solution.

How are these files generated?  Is it an automatic machine generated file 
with a machine generated name?  If so is there some pattern to the naming 
convention being used?

If it's a human generated, is it possible to just have the file saved as the 
same name each time?



"Paul" wrote:

> I have to say, Lance, this goes somewhat over my head...to put it
> mildly. I do appreciate your time on this, though, but somehow I feel
> this is getting too complicated (for me anyway). I have been thinking
> this over (thanks to you and another expert) and I figured that the
> files I do not need all have in common that they were downloaded on a
> different date than the files I do need, which will always be
> downloaded the same day. Could I use the date criteria somehow as a
> condition to import or not? If possible, that seems easier to me.
> Thanks again for your time. Paul
> 
> 
0
Utf
10/26/2007 2:10:01 PM
The files are downloaded from an external source and the naming logic
includes their internal request number, which makes it impossible to
foresee. The files are manually uploaded and then saved to the
directory, but to install a naming convention there woudl be error
prone, because I'm not the only one that runs the reports (in fact, I
designed it so that other could run it as well) and I am the only one
with a little Access knowledge.

0
Paul
10/29/2007 10:16:34 AM
If you go with the "static file names" it wouldn't be difficult to check if 
the files have current data before processing them.. for what that's worth.

The only other options I see is to make a form which prompts users to choose 
the files they upload.. and then make running that form part of the file 
creation process.  A lot less automated though.

"Paul" wrote:

> The files are downloaded from an external source and the naming logic
> includes their internal request number, which makes it impossible to
> foresee. The files are manually uploaded and then saved to the
> directory, but to install a naming convention there woudl be error
> prone, because I'm not the only one that runs the reports (in fact, I
> designed it so that other could run it as well) and I am the only one
> with a little Access knowledge.
> 
> 
0
Utf
10/29/2007 2:32:01 PM
So maybe I can let the user choose himself and th=E8n do a date
validation. That would enforce awareness, prevent mistakes and
overall, it will not stall tohe process too much. Thanks, Lance, for
your ideas and time. Much appreciated, Paul

0
Paul
10/31/2007 8:34:46 AM
Paul:
i don't understand your RE: to my ? about an Epson pinter color II model 
P880A: it was giving to me and the first time printed OK, but after that 
prints # and crazy characters as long as there is paper in the printer.tia 

0
hoolio
11/2/2007 1:20:30 PM
Reply:

Similar Artilces:

Can i use Windows Search to find a number within Excel?
If i use Widnows search of the CONTENTS of files (not file names), i find in Excel, that for cells that contain text, they can be found. But, it does not appear to me that numbers can be found. Of course the number as Excel stores it may differ from how it is displayed. But, does anybody know if this is possible? For instance, i have a cell with the number 3,709,227 in it, as it appears in Excel (as formatted). I tried typing the number both with and without the commas, into WIndows search, but Windows search still does not find the Excel file that contains that number. So, any kn...

Excel 2007: Problem with refresh screen (when minimizied)
Hello On our network (around 40 workstations; Win XP Pro and Office 2007) we have particular the following problem: The user minimized the Excel programm window and do some other works in other applications, return to the minimized Excel window and want to maximize the Excel window. Result: The Excel window is empty. You can paricular see some rows or colums but often not. We try out also other graphic card but get no success. Any ideas? Kind regards, Martin Schweizer ...

excel 97 filename truncation in Windows XP Pro
I have a legacy application that runs in Excel 97. I have Excel 97 and Excel XP loaded on a new machine that runs Windows XP Pro. The legacy application fails because the filename of a workbook is truncated during the saveas command. When I do a manual saveas in Excel 97, the filename truncates to the first letter. If I do the same thing in Excel XP, the saveas works normally. There have been a few posts describing the same problem. I searched google and found this: http://google.com/groups?threadm=020101c30916%2442320250%24a501280a%40phx.gbl The last post in the thread suggested an answe...

Office 2003 Opening a new Excel Doc.
I just updated to Office 2003 and everytime I open up a new workbook a blank workbook name PERSONAL opens as well. How do I change that from happeneing?? Thanx, Jesse Hi Jesse Window>Hide and save the personal.xls When you record a macro you have the option to save the macro in this hidden workbook so you can use the macro in all your workbooks. You can find the file in your Xlstart folder -- Regards Ron de Bruin http://www.rondebruin.nl "Jesse" <Jesse@discussions.microsoft.com> wrote in message news:65505D55-31EB-4BAA-A32D-B052F70E03AD@microsoft.com... >I just...

Newbie struggling
Hi For coursework I have to create an excel workbook that takes totals from invoices and adds them all up - pretty simple for some but not me! I have created the invoice template and the user enters the necessary data to complete and print it out - what I need is a way of taking four or five sets of data from that invoice sheet (e.g. invoice number, customer name etc etc) and writing them to another sheet so that I can add them up. The problem I am having is how to get that data into the next blank row in the second sheet - currently it only overwrites the first set - how do I get the pointe...

Two Excel tables in MS Query
How can I link two tables in two Excel-files, in MS Query and result view in PivotTable? ---------------------------------------------------------------------- I can link two tables in one Excel-file. ActiveSheet.PivotTableWizard SourceType:=xlExternal, SourceData:=Array( _ "SELECT `TKG_DATA$`.YEAR, `TKG_DATA$`.MONTH, `TKG_DATA$`.DAY, `TKG_DATA$`.TIME, `TKG_DATA$`.`INC/OUT`, `TKG_DATA$`.`C59/170`, `TKG_DATA$`.`C46/158`, `TKG_DATA$`.`C356/137`, `TKG_DATA$`.`C355/358`, `TKG" _ , _ "_DATA$`.`C354/357`, `TKG_DATA$`.`C32/142`, `TKG_DATA$`.`C30/116`, `TKG_D...

importing statements #2
I'm getting an error when trying to import my bank statement. It reads "A transaction in the QIF file has a date that could not be resolved and therefore was not recorded into your Money file. Check your Regional Settings in the Windows Control Panel to make sure they match the date format of the QIF file, and then try importing the file again." Please help! Thanks ...

Merging excel office data into a publisher office project(Australi
Having problems when I get to the point in the merge wizard "catalog merge template", with the DOB format to be entered into the membership card that I have made in Publisher. It reverts back to American date format. Could the problem be in the edit record section? All seems to be okay till then. I have checked the regional settings etc., all okay. When I have finished template of merge & highlighted the DOB field gone to language on tool bar and set language it still doesn't work then preview, it has reverted to American. ...

how do I tell excell 2003 to print sheets back to front?
In excel 97 I could tell printer to start printing from the back page to the front so the job would come off of the printer in the right order. I cannot find that command in excell 2003. Am I overlooking it, or have they changed the command convention to an obscure, hard to understand, jargon. FILE | PRINT... | Click on the PROPERTIES button | PAGE ORDER, i.e. back to front. "goochiepepper" wrote: > In excel 97 I could tell printer to start printing from the back page to the > front so the job would come off of the printer in the right order. I cannot > find that co...

How can i get more 3D shapes for Auto shapes in excel?
Please suggest how i can get more shapes (simple as well as 3D ) in Auto Shapes of drawing toolbar in excel & word? ...

Export Excel file to SQL
I'm currently doing a project where it require client to export excel spreadsheets to SQL. the application that is using the excel spreadsheet exported is a web-based application. Is it possible to do that? Any suggestion or recommendation is greatly appreciated. thank you Go to Enterprise Manager. Browse to your database in the left pane (object browser). Right click your database. All Tasks --> Import Data. Next. Set your datasource to Microsoft Excel 97-2000 or whichever Excel version you have. Click the ... button next to the File Name text box and browse to your Excel sheet. M...

Excell Cells: Auto Formatting
I have protected a workesheet and unfortunately when the people submit information on the cells and it is too long than the actual height of the row and it wont extend. What can I do? When protecting the sheet allow users to format cells and rows. Gord Dibben MS Excel MVP On Tue, 9 Feb 2010 13:14:01 -0800, Sophie <Sophie@discussions.microsoft.com> wrote: >I have protected a workesheet and unfortunately when the people submit >information on the cells and it is too long than the actual height of the row > and it wont extend. What can I do? How do I jus...

RE: Check out this important package from M$
--ymlnvvfgmuljc Content-Type: multipart/related; boundary="dojsubxijhqfgbi"; type="multipart/alternative" --dojsubxijhqfgbi Content-Type: multipart/alternative; boundary="baxgpvgkfadysvrwp" --baxgpvgkfadysvrwp Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft User this is the latest version of security update, the "September 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express. Install now to help protect your computer from thes...

How do I create a drop down list in Excel 2003
How do I create a drop down list in Excel 2003. I've tried to use the help menu and I cannot figure it out. See: http://www.contextures.com/xlDataVal01.html -- Regards, Peo Sjoblom "Lenny" <Lenny@discussions.microsoft.com> wrote in message news:8F05DEA1-E264-4760-9DDE-3C97319AA1D4@microsoft.com... > How do I create a drop down list in Excel 2003. I've tried to use the help > menu and I cannot figure it out. ...

Watermark in excel 2000
Hi I'm trying to insert a watermark from a jpg file. The probem is the watermark always stays on top and cover the text, no matter I use the "send to back" button on or the item. TIA /Per Hi maybe the following sites will help you: http://www.xl-logic.com/pages/formulas.html http://www.rondebruin.nl/files/Watermark.zip And for Excel 2002+: http://cpap.com.br/orlando >-----Original Message----- >Hi > >I'm trying to insert a watermark from a jpg file. >The probem is the watermark always stays on top and cover the text, no >matter I use the "send ...

Triming the fat off my sheets...
Ok I have a spreadsheet with 100 items and another one with 150. The two sheets match up exept for the 50 odd balls. How do I get the the first sheet to line up with the second on one sheet. Hi not quite sure but try: http://www.cpearson.com/excel/duplicat.htm#ExtractingCommon -- Regards Frank Kabel Frankfurt, Germany Neal wrote: > Ok I have a spreadsheet with 100 items and another one with 150. The > two sheets match up exept for the 50 odd balls. How do I get the the > first sheet to line up with the second on one sheet. ...

Maximum value to use autofiler feature in excel
Hi, does anybody know the maximum value to use autofilter in ms excel? I heard my friend told me that excel could filter only select data is less than 1000. He told me that he has 4000 row and use autofilter, an expected record is more than a thousand but the record display only 1000 maximum. I want to test like him too but I don't have any file that contain alot many thousand of record. Does anybody have any idea? Hi see: http://www.contextures.com/xlautofilter02.html#Limits -- Regards Frank Kabel Frankfurt, Germany "Roath Kanel" <012633023@mobitel.com.kh> schrie...

how to sort cells with sheet reference
i an unable to sort the data in cells containing a reference to anothe sheet. it seems as though excel is treating this reference as absolut and not relative. is there any way to do this, such that thes equations adjust to the sorting? any thoughts would be appreciated. thanks -- vba_neophyt ----------------------------------------------------------------------- vba_neophyte's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=2418 View this thread: http://www.excelforum.com/showthread.php?threadid=37804 one way would be to copy and paste specail values only - a...

Importing a CSV file
I want to import a file into physical inventory. Here is what I do: 1) Run a Quantity report(I don't think the report really matters) 2) I put it in alphabetical order, then I export it using RMS Manager into a ..CSV file 3) I go into Inventory > Physical Inventory, and I click on New and Manuel Entry 4) I import the file...it works just fine!!! BUT If you in any way alter the .CSV file, and save it again, in the same format Steps 1 - 4 won't work...WHY WHAT THE HECK AM I DOING WRONG??? -- Thank You Vince :) The problem might be the Item Lookup Codes are being modified by ...

Excel 2000 #38
Hi - I use Excel 2000 and wish to display negative number, accounting and currency values in parentheses. However in the Format Cell function in Excel there is no option to use parentheses. The only option is to use the -ve symbol or to display the value in red. How do I display the value in parentheses. -- Mike from Australia Hi Mike Try Format>Cells>Number>Custom $#,##0.00;[Red]($#,##0.00) Regards Roger Govier Mike wrote: > Hi - I use Excel 2000 and wish to display negative number, accounting and > currency values in parentheses. However in the Format Cell functio...

Counting conditional formatting
Re my earlier post, I am tring to fond a formula to count those cells that have been formatted by conditional formatting. On Sat, 16 Oct 2004 17:13:01 -0700, "Peter" <Peter@discussions.microsoft.com> wrote: >Re my earlier post, I am tring to fond a formula to count those cells that >have been formatted by conditional formatting. I have no idea what your earlier post was, as this post appears in a thread by itself. But to count those cells that have been formatted by conditional formatting, you will need to use the same formula that you used for the conditional form...

Excel 2003 ODBC Add-in
Where can I download Excel 2003 ODBC Add-In from ? Hi MS no longer delivers this addin with Excel 2003. But check out: http://www.bygsoftware.com/examples/sqlrequest.html http://office.microsoft.com/downloads/2002/xlodbc.aspx -- Regards Frank Kabel Frankfurt, Germany "Excel2003User" <Excel2003User@discussions.microsoft.com> schrieb im Newsbeitrag news:687E3600-63A2-414E-A7B8-5366BE386667@microsoft.com... > Where can I download Excel 2003 ODBC Add-In from ? ...

DDE link error Excel freezes
Hi, I am using Excel 2003 and in one sheet I have a DDE link from anothe program. When I open the sheet I get a question wether I want to updat the fields or not. When I click YES the sheet normally opens and I ca view the data. But sometimes it seems like Excel freezes and I have t wait 3-5 minutes until it resumes. Anyone that has any idea why? The source program is always started first by the way. Kennet -- Message posted from http://www.ExcelForum.com ...

sum several sheets and different columns
Hi all, Any ideas of how to get this formula not so ugly: =SUM(V5U!R5+V5U!S5)+(V5U!AA5+V5U!AB5)+(V5U!AJ5+V5U!AK5)+(V5U!AS5+V5U!AT5)+(V5U!BB5+V5U!BC5)+(V5U!BK5+V5U!BL5)+V6U!BM5+V7U!BM5+V8U!BM5+(V9U!I5+V9U!J5) I have one week in every worksheet and trying to get a summary of month in an easy way. Thanks in advance //Thoma -- Message posted from http://www.ExcelForum.com If you are just looking for neatness you could: Define local named ranges V5U!MyRange1 refers to =V5U!$R$5:$S$5,V5U!$AA$5:$AB$5,V5U!$AJ$5:$AK$5,V5U!$AS$5:$AT$5,V5U!$BB$5:$BC$5,V5U!$BK$5:$BL$5 V9U!MyRange2 refers to =V9...

Excel 2003, Opening a file
Hello Everyone, I have this problem when trying to open a file in Excel. It takes along time to drill down down in the folder list. Any ideas? Thanks -Mark I suggest putting a shortcut to the file(s) or to the buried folder that your file(s) are in. The shortcut could be on your desktop or in some folder close to the "surface". To create the shortcut or your desktop you can navigate to the desired folder (using My Computer), right click and drag the folder to your desktop. When you release respond that you want to create a shortcut (rather than moving or copying...