Excel rows sorted when imported to Access
Thank you for taking the time to report an issue.
What's wrong... Please write below.
This import file can contain but not have to couple of columns. Also it might contain Headers or not and lastly it might have some empty rows between each serial.
This data is then processed and then the user selects where to save the exported file with a lot of information in it.
Now I have solved the issue of prompting a user to choose which column the data starts at (ex. A1 or B1 etc.)
Now I have two questions that I need help with and I tried searching online but had no luck.
During the wizard stage I would like to include a check mark that would be IF its checked then it will know that this imported file has headers and it will start at A2 instead of A1!
My second question is about the blank rows, when I import the excel file if there are empty rows between filled in rows is there a way to skip them and then on the export sheet just showthe rows that were there on the first excel sheet?
(I've reduced the macro to just that one function so I know the problem is somewhere in the TransferSpreadsheet action
I try to put # to both side of the links in excel as someone suggested earlier, but not work.
and I also tried to save the excel as web page before importing as .html document, but access seems not be able to find the document.
So for example: CustomerID, FName, LName, Address, City, Province, PCode, PhoneNo, Cell, Event. The customer only ever has one event connected to him.
My background with Access really wants to make the relationship the other way: Event with the customer connected using a form and subform, etc.
Anyhow, so this is the way I did it because info is always imported from Excel could be once a week.
My issue is that because the event field in the Customer table is a number field (because it connects to the primary ID key field in the Event table which is an autonumber field) when I try to import some sample data to test it, it always skips the Event information from Excel.
I tried tricking Access by formatting the field in Excel to be a number and even tried leaving the top rows in Excel as numbers and my real info underneath, it simply does not work.
If worse came to worse, the customer could be matched to the eventafter importing the Customer info, but I was hoping this does not happen because there are about 200 customers imported at one given time.
The requirements are:
-The code should allow me to choose whether the first row has fields
-Browse the Excel file (cannot be specific because this excel file may change)
-Choose the Excel file and specific sheet to transfer (cannot be specific because this sheet may change)
-Skip 'n' rows in order to get to the data of the excel table
-Transfer the sheet into an access table
-Delete original file that was imported
I attached what I have so far but it's missing two requirements: Skipping rows and being able to choose a specific sheet instead of just a folder that contains the excel file.
They are using Access 2003 and Excel 2007, 3.5GB RAM
It works perfectly on my machine - Excel 2010 and Access 2007-2010, 4 GB RAM.
The file is imported in Excel 97-2003 format via a macro using the transferspreadsheet function.
It returns error 3274: "external table is not in the expected format."
There are 1488 rows and 71 columns in the spreadsheet and resulting table - in future additional columns may need to be added representing new critical data.
The interesting thing was that it worked fine on my machine. Then as a trial and error process I cut the file down to 26 columns and it worked fine. 52 columns also imported. But it gave up when there were 71 columns.
if 52 columns * 1488 rows =77,376 record worked, that's more than the magic number of 65,536. but it doesn't like 71*1488 = 105648 records. Is there a limit at 100,000, or some other number in between? I would have thought Access could hold/import much more than this. Am I missing something?
Part of the Excel information that is imported to a new table includes the employee's name with work details in each row.
In my access report I do not use the employees name, I use their employee ID number. I have a table in my access database that contains 2 columns. The employees Name and their associated Employee ID number.
After I import the Excel information to Access how do I set up a macro or programming that will look at the name in each row and change it to the correct Employee ID. I could have anywherefrom 20 rows of recods to 600 rows of records.
Since Excel 2007 has capacity for upwards of 1 million rows why can't I export a huge Access table
The sheets I have looked at have headers and the data doesn't start till row 12 or so. Only one sheet per file.
Here is what I need to happen:
The excel files, all of them, would need to be opened and have a copy paste special to get rid of the formulas in the excel files, then saved.
Then all of the files in a specified directory would need to be imported into a specified table in access.
I don't know much about doing this in vb, but I am thinking that is where this needs to be done.