ACCESS FEED: Re: [MS_AccessPros] Re: Problem copying/importing from Excel

Kamis, 23 April 2015

Re: [MS_AccessPros] Re: Problem copying/importing from Excel

Adam,

First, I was suggesting that the upload code be written in Excel so that a button could be pressed in Excel that throws up a standard file open window to allow for the selection of any file you wish.

With this possibility in mind, the following ought to be considered.
1. Even though the Excel columns get to be different each month, it sounds like they just get shifted as a new month's data is added. Even though a column shift occurs, are the column headings consistent across months? For example, February is still February regardless whether the file and an April or May file.
2. If number 1 is true, then is the "AcctNo." heading always present. If so, then a quick test of the file when it's opened for this heading can verify that at least the basic file contents are what is needed. This would not prevent one from opening April twice so that May is skipped.
3. I'm picturing some constant data for each account in the left-hand columns with each month's data in the 12 columns to the right. If this is accurate, then determining where to place uploaded data in the Access table becomes one based on the column headings where my assumption that even though the month data is shifted across columns the Access table's data is constant.

Given number 3, it may be necessary to upload the Excel data field by field but the only downside to this is run time. I still don't know the average number of Excel records so don't know if run time is a consideration. Clearly 50,000 records will be uploaded quicker than 500,000.

Based on my limited knowledge, I'd build the Excel based tool to open the file, validate the file's authenticity, upload the data into the Access table, and report record counts or errors on a worksheet.

Jeff

-----Original Message-----
From: "runuphillracing@yahoo.com [MS_Access_Professionals]"
Sent: Apr 23, 2015 9:51 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Re: Problem copying/importing from Excel

Bill, That's annoying. I see why it might do that if it were importing to a new table. However, why doesn't MS let me decide what the data is? Can you tell them to fix this bug?

Jeff, I'm familiar with automating Excel including doing so from Access. This is a file I get sent monthly, not one I create. The file has a different name each month (e.g., FileMarch.xls, FileApril.xls). The column headers (row 1) are similarly different each month. It's a rolling 12-month report of revenue and volume by AcctNo. Different types of accounts have different types of alpha-numeric formats. There is one type that is numeric only.

I suppose I could write code in Access that would add a leading single quote to that column, then import it. I'd have to manually enter the name of the file. I think that would be easier than having to rewrite the query copying from the new table to my working table each month (because the field names in the new table would be different each month).

Thanks

Adam