Hello,
I'm trying to import xls files in access, but it always gives a failure notice: .xls contains no object. What does this mean? Please help me - urgent!
Thanks.
please e-mail me (karolien.hellemans@leuven.be)
I have a stock control database which i have nearly completed. This has Manufacturer, which is linked to products, which is linked to Sub Product(which also has field partCode). i.e. Manufacturer1 can have 3 products, and each of these products could have 5 subsystems and partcodes. Each partcode is unique to that subsystem/product/manufacturer.
I then have a pricing spreadsheet in excel, which has many tabs. A new column has been added for each item for Manufacturer,Product,Subsystem and Partcode.
I need to import these manufacturers,products,subsystems and partcodes, but into the tables with the correct relationships, i.e. product1 and product2 are products of manufacturer1 and so cannot come under manufacturer2, and so on.
I hope this makes sense, Thanks in advance for any help you can give!
Emily
Has anyone ever done an XML import into an Access database. Is it involved?? Can anyone give me some hints or tell me where to start?
View 1 Replies View RelatedSo I have inherited 6 .WDB files that I need to get into my database and create some forms with. Problem is, I don't have MS Works to save as another file type. getting these .WDB databases into Access would be amazing!
View 2 Replies View RelatedHi everybody! :)
After a week of hair pulling problems with no solution, I've decided to ask my question here, knowing someone will be able to help me...
To the point:
I'm to use a QBF with more then one criteria,
(I'm using: like "*" & forms![f_name]![TB_first_name] & "*" or is null),
I'm having a problem with this because when I leave a text box blank, it retrieves everything (because is null = true), what I'm trying to say is: I have a QBF when using, lets say, 3 text boxes, unless I fill all of the text boxes with text, the query retrieves all of the records, can it be fixed ?
I've tried to use IIF but with no success... can anyone help ? :confused:
and another problem: while trying to import data which is not in English, from a Visual FoxPro data base to access, it turns out unreadable, what can I do to transform ASCII to ANSI or Unicode? :confused:
p.s. - Sorry for my English
TIA
WildCat
Hi, I’m trying to Import an external CSV file in to Access and then Update/Add the record into a table. I need to be able to do it using SQL and I’m not allowed to touch RecordSet!
Does anyone know How I could do this or where I would be able to find help on this.
Thanks
coops1204
Hi... i have a excel file..... from which i have imported records.... after importing.... the records sequence in my table has changed..... can anyone tell me how to preserve the sequence that i had in excel..........
View 3 Replies View Relatedhow to import text file row by row into access?
pls help me out......
Hello all, I am all new to access and I am trying to convert a program I made from excel VBA to ASP.net using access data bases. I am not even sure if this is possible, but I started looking into access couple of days ago, and reading examples of using it for websites etc. that I think it might have the potential.
My question is:
I have an excel file that has the following format:
months 1 2 3 4 5 6 7 8 9 10 11 12
Products
product1 20 5 9 8 etc
product2 9 6 7 3
product3 8 4 2 1
product4 100 26 78 32
.
.
.
Now the way I pull data out of this sheet of excel is if a user asks for the total number of products sold in february, the program will search for the month (2 in this case and that would be the fixed column) then it will add up all the entries in that column after row 1 (row one is the row that has the month numbers) and display the total (for this example it would be 41).
This is a real simplification of what is done but I think is brings out my question of how to build a data base for such data where I would need to make a 2 dimensional search, since from what I could not figure out how to incorperate my months into the data base in access when I tried to convert the file from excel to a database table in access.
I hope someone could help me. and I thank you all for you comments and replies in advance.
how do i Import the data from text files so that i can create the database structure?
for example i have 3 files called - data.txt, booking.txt and, other.txt and want them imported into access?
this is because these tables will be holding all the entities etc
Thanks in advance
I'm trying to import an excel sheet to access and one field is y or n and I am not sure how to convert this so the value is right so I can query it.What should I put in my excel sheet "yes","no' or Y,N or numeric values??Access doesnt show it help please!!!
View 1 Replies View RelatedHey,
I have an access database that imports a certain range of a certain sheet from 30 files all of which are in the same folder and I am trying to get that BOTTOM MOST peice of code to automate the process.
Now one of my many complications was that the excel files are workbook protected, and so it would give the 'cannot decrypt error' that’s why I have all that code regarding unprotecting it... but for some reason its not working
It gets to the line where it gives it the password and it tells me the password is unaccepted, and yes the password is correct...
It must have something to do with the do loop, because it works fine whenever I used it over one import file and thus without the loop... Like this for ex works perfectly fine
Code:Public Function TransferSP()ExcelFile = "G:CBTFILENAME"On Error GoTo ErrTrpStartAgain: DoCmd.TransferSpreadsheet acImport, 8, "TEST", "G:CBTFILENAME", True, "Access_Upload!C13:L34"xlapp.ActiveWorkbook.Protect (blah)ErrTrp:If Err.Number = 3161 Then 'Encripterror so unprot wb xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events xlapp.workbooks.Open ExcelFile 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel GoTo StartAgain 'Try and Import againElseEnd IfEnd Function
So Please tell me what is wrong with this bottom piece of code...
And secondly, I have a table which contains a list of names, whether they are active (checkbox) and their filenames each on a column of its own...
Can anyone show me how i can include into my code (assuming the password problem is fixed) that would allow me to place an if statement that checks whether the user is active or not... IF he is then it imports his file (using the filename column next to his name).... If he is not active then it just goes onto the next person without importing him/her
Right now I have it importing all the files in that folder
Code:Option Compare DatabasePublic xlapp As New Excel.ApplicationPublic Sub ImportAll() Dim strPath As String Dim strFileName As String strPath = "G:CBT" 'Set Path strFileName = Dir(strPath & "*.xls") 'Set first file Do On Error GoTo ErrTrp DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" ErrTrp: If Err.Number = 3161 Then 'Encription error so unprotect workbook xlapp.Visible = False 'Open Excel xlapp.EnableEvents = False 'Disable Events (Macro's) xlapp.workbooks.Open strPath & strFileName 'Open File xlapp.ActiveWorkbook.Unprotect (blah) 'Unprotect 'Try and Import again DoCmd.TransferSpreadsheet acImport, 8, "Test 2", strPath & strFileName, True, "Access_Upload!C13:L34" xlapp.ActiveWorkbook.Save 'Save xlapp.EnableEvents = True 'Enable Events xlapp.ActiveWorkbook.Close 'Close File xlapp.Quit 'Quit Excel Else End If strFileName = Dir() 'look for next file If strFileName = "" Then 'no more files Exit Do End If LoopEnd Sub
Please let me know what I can do with this
I am a novice at coding...Just tell me where to put the code
Thanks
Ayyad
Hi, I am importing a custom field (containing six digit integers, e.g. 000099, 012000, 102300, ..) from Excel. This custom field in Excel is formatted 000000.
The corresponding field in Access is a text box with input mask !000000. (the Format property for this text box is blank). However, on importing, 000099 enters as 99, 012000 as 12000 etc.
What have I done wrong! Any help very much appreciated.
mcchu
I'm importing .csv and .xls files into access using docmd.transferspreadsheet and it's simple enough except for a couple of issues I'm having:
1. The .csv file has leading ' and if I converti it .txt if has leading " in some of the columns .....what's the best way to get rid of these and is it better before or after the importing process.
2. a .xls file has a few rows of totals at the top so I need the importing to start at row A7 instead of A1...is there a way to control where it starts rather than manipulating the file prior to importing because it's all going to done by clicking a button on a form by user.
And there's a 3. one of the .xls has additional blank rows that it seems to import into the access table....what's th solution for that.
All the files will vary in number of records on a daily basis so I can't specify range like the DoCmd.
Hi, I have an xml which fields of text and some of numbers.
When I do: File --> Get External Data --> Import; I'm able to import the file.
The problem is that not all the data is entered in the fields. The colums containing the text are excluded.
While importing, I did choose the option "Structure and Data", so it should work.
Any suggestions on to why it is doing that?
Thanks.
Does anyone know how to import Microsoft Project data into an Access database?
I am looking to use "high level" MS Project schedule data as input to a new Access application that I am currently designing. Is this even possible?
Any help or links to more information is GREATLY appreciated!
Thanks,
AC
I have a dbf file that I need to somehow get the table data out of, and into access, excel, or some other usable format. I searches the MS Knowledge Base and read several articles on updating the Jet 4.0 drivers (up to date) and updating Foxpro ODBC drivers, etc. I also searched these forums, and I have yet to find any solution that works.
I think this dbf file might be Foxpro, but I'm not sure. I don't know anything about Foxpro. I was getting an error message "unexpected error from external database driver (8961)", until I read an MS article stating that I should change the name of the Borland folder to BorlandOld and try re-importing. Now Access 2003 just says: "external table not in the specified format".
I wanted to know if anyone else is having issues with getting external data into access from excell? For some reason this function is not working for me today.
Any help would be greatly appreciated.
Thanks
If the data in the spreadsheet is not in a standard row/column datasheet format how can I import it to Access, does anyone know, please help if you do. I have a formatted excel form and i need to import it to access!!!!!!
View 1 Replies View RelatedHow can I import a Word table (6 columns by 800 rows) into a new Access database? Thanks.
View 5 Replies View RelatedGood morning all,
i have a feeling this could be a simple yes or no answer,, at the moment i am using the 'Import Data Wizard' to pick-up an excel file that is up-dated on a daily basis, this file always sits in the same directory and always has the same file name, ideally i would like to have an action button that draws the data in without having to use the wizard each time,, does anyone know if this is achieveable? if so, anyhelp would be much appreciated.
as always, many thanks
Hi
I've had a search through but to no avail.
I'm trying to import data from an Access query into a blank spreadsheet (Data-Import External Data etc), but it's only giving me a list of the tables in the database and not listing any of the queries. I've never had any problem with this before - I've been able to import queries fine - so I hope someone knows what's going on.
Access and Excel 2002 by the way.
Hi,
This is my first post here so please be kind. I have been tasked with analysing data within Access. I currently have a blank database and each month I will need to import approx 600 spreadsheets and then run some queries and output back to Excel.
My problem is that the Spreadsheets are in an odd format (column / row headers) and I am having trouble getting these correctly into access so I can run queries.
Thanks
Andrew
I tried posting this before, but I have not seen it posted yet, so I will post again.
I am trying to import an excel sheet into access.
I have searched the threads, and found similar problems, but none of the answers are helping.
The problem:
I use a macro to transfer the spreadsheet (Transferspeadsheet)
I have a column in excel that contains mostly numeric entries, but sometimes it will have alphanumeric entries.
Access will see the numeric entries and assume this is a numeric field- and then null my alphanumeric entries, and I will get importerror tables after the fact.
Many times the first 10 rows are completely numeric, but the 20th row is alpha numeric. If the alphanumeric row is closer to the top- no errors.
I have 60 spreadsheets to load every morning. Switching to access for all users would be a godsend, but also a fantasy in my environment. I have to find a work around for this. The users will always be using excel to enter this data.
I have tried:
1) Changing excel columns format to text.
Result: Access doesn't care. Sees the field as numeric- import errors on alphanumeric entries
2.) Linking to table
Result: if the first rows are numeric, when I scroll down to the alphanumeric entry it states "error" when I view the linked table in Access. (Access will not allow me to change the field properties of the linked table)
3.) Making a table in access with the "correct" feilds and exporting to excel. Having the users use that version.
Same result- import errors.
There is one thing I have not tried, and I am ready to try anything:
Somehow exporting excel to text and then importing the text.
HOWEVER: I read this as a solution someone has tried in this forum- but he had problems with date entries. (They didn't convert very well, I am assuming).
I have date entries in my spreadsheets, so I don't want to go there. Besides, I don't know how to automate the export of 60 spreadsheets to text.
I asked someone else, he suggested an "import map".
Did help search, Internet word search- nada.
Is there some sort of import template that can be used to force access to understand that I want a particular field as a text field?
I'm using the following tools/systems:
* Oracle 9i Enterprise Edition Release 9.2.0.5.0
* Microsoft Access 2002
* Access is running on a XP Professional desktop
What I'm doing at the moment is:
* Linking the Oracle tables in Access via ODBC
* Using Access to run develop & run queries against the linked Oracle tables
What I'm trying to figure out:
* What is the easiest way to export the query results to a local (on my machine) Access table?
Some of the queries return < 65000 rows, so I can export to excel & import the excel file back into access. Unfortunately this doesn't work for the bigger queries.
Suggestions?
I can't figure this one out. i have a spreadsheet called Skills. It the following layout
NAME CITY STATE SKILL1 SKILL2 SKILL3 SKILL4
joe la ca 1 2 2 3
fred seattle wa 2 1 3 2
alice irvine ca 3 4 2 2
There are 100 skills and hundreds of rows of names.
I'm trying to get the data into Access so I can build a simple form to do searches. I'm thinking I need 3 tables in the database. One for all the Names, City, State. One to contain all the skill names. One to contain all the numbers that pertain to the indiviuals skill level for a certain skill. I can get the skill names into a table and I can get the names, ctiy, state into the second table. I can't figure out how to get the levels (numbers) into a third table and tie all three tables together.
Any help would be MUCH appreciated.
Thanks