Categories

Main
Database
Java
Microsoft.NET
Adabas
DB2
Informix
Microsoft SQL Server
MySQL
Oracle
Pervasive.SQL
PostgreSQL
Sybase
Other
ASP
ColdFusion
Crystal Reports
Delphi, C etc
JAVA
Microsoft.NET
Perl and the DBI
PHP
ANSI SQL
Unix Shell Scripts
Visual Basic
XML & XSLT
Corel Paradox
FileMaker
Microsoft Access
Microsoft Excel
Other PC Databases
Applications & Tools
Database Concepts & Design
EJB programming & troubleshooting
EJB design
General J2EE
XML & Web services
Web tier: servlets, JSP, Web frameworks
Performance and scalability
Industry news
TSS feedback
Mobicents Contributors
Mobicents Users
JSLEE Resource Adaptor Types
Planning JavaOne 2008
Sun Tech Days
Other Java conferences
Binary Web Services and XML
Metro and JAXB
GlassFish
GlassFish Plugins
Project jMaki
GlassFish WebTier
Mural
Java Development Tools
Java WS & XML Community News
JAXP
Java SE
6uN Early Access
Java Quick Starter
Java SE Snapshots: Project Feedback
JCK Forum
Feedback and Suggestions
JDK Distros
JDK Open Source
General JSR Discussion
JCP 2.6
JXTA Community Forum
ME Interest
ME Cool Apps
ME General Help
ME Feedback & Suggestions
ME Application Developer Interest
Blu-ray Disc Java
ME Developer Days
Squawk
Mobile Developer Alliance
OpenCable
LWUIT
JavaFX Script Language Discussion
OpenJFX General Discussion
Scene Graph
General Performance Discussion
Your Java Career
NetBeans 6.0
Servlets
JSP
JSF
Portals and Portlets
EJB and Other Java EE Technologies
Distributed Java
Object Relational Mapping
JDBC
Web Services
Swing / AWT / SWT / JFace
JNLP and Web Start
Java Micro Edition
Sockets and Internet Protocols
Threads and Synchronization
Performance
Applets
I/O and Streams
Other Java APIs
Game Development
Java in General (beginner)
Java in General (intermediate)
Java in General (advanced)
Programmer Certification (SCJP)
Developer Certification (SCJD)
Associate Certification (SCJA)
Web Component Certification (SCWCD)
EJB Certification (SCBCD)
Mobile Application Certification (SCMAD)
Architect Certification (SCEA)
Web Services Certification (SCDJWS)
XML Certification
Product and Other Certifications
Mock Exam Errata
Sun Certification Results
Authors' Corral
Book Reviews
Events
Bunkhouse Porch
Teachers' Lounge
Testing
OO, Patterns, UML and Refactoring
IDEs, Version Control and other tools
Ant, Maven and Other Build Tools
Linux / UNIX
Mac OS
HTML and JavaScript
XML and Related Technologies
Agile and Other Processes
General Computing
Security
Groovy
Scala
Other Languages
Struts
Application Frameworks
Other Open Source Projects
BEA/Weblogic
IBM/Websphere
Oracle/OAS
Apache/Tomcat
JBoss
Other Java Products and Servers
JavaRanch
Cattle Drive (java college)
Moderators Only
Trash Can
Jobs Offered
Jobs Wanted
Jobs Discussion
Meaningless Drivel
Programming Diversions
Blatant Advertising
Java Announcements
New To Java
Advanced Java
Java Applets
Networking
Threads and Synchronization
Java 2D
AWT / Swing
SWT / JFace
CLDC and MIDP
CDC and Personal Profile
Sun Java Wireless Toolkit
Enterprise JavaBeans
JavaServer Pages (JSP) and JSTL
Java Servlet
JavaServer Faces
Web Frameworks
Database
XML
Lucene
NetBeans
Eclipse
IntelliJ IDEA
JCreator
Other IDEs
Java Tutorials
Java Tips
Jobs Discussion
Jobs Offered
Jobs Wanted
Professional Certification
Forum Lobby
Java Blogs
Introductions
Reviews / Advertising
Suggestions & Feedback

Resources

Java Database
Linux
Coding
Mobile
Hardware
Software Development
Software Development
iOS,OS X
iOS,OS X
ORACLE
IBM DEVELOPER
IBM DEVELOPER
MSDN
MSDN


Tags

Microsoft Access

The database that comes with Office Professional. Access can handle tens of thousands of rows with ease and has great connectivity support for traditional database servers.

Import Excel Range into a table


Hi, I have been asked to look at this issue and I am unsure if it can be done, if it can be done then how is it possible for it to be done...

I am wanting to import data from 5 different sheets in an excel spreadsheet. All the data is on the same layout on each worksheet from a range of A21:BF42, A76:BF97 & A131:BE152.
I need to import this information on a weekly basis, to an import table in a database and then I can query the information from there. My final problem is that the excel spreadsheet that I want to extract the data from is sent from an outside source and has been password protected. 

How can this be done?

Jez

Try with MAKE TABLE QUERY, or APPEND QUERY.

MStef-ZG, Thanks for your reply but I dont understand what you mean...

My data is currently in excel and I want to understand how its possible to whats in the above thread and import it into a database.

Jez

Why not just import all the spreadsheet data and then use a query within Access to only view the selection you want?
Depending on the size of the data of course.

Look at "DemoVidi1A2000.zip" (attachment).
Here you have got EXCEL "Vidi_1" and Access mdb "DemoVidi1a2000.mdb".
In MDB look at table. There are a link on EXCEL table, you MUST TO DO A
RELINK THIS EXCEL TABLE, (depending on your Path), and you have got an
access table "tblVidiAcc" (empty table).
You have got a "Query1MakeTable". Run this query, and see in "tblVidiAcc".
Adapt it on your mdb.

MStef-ZG, Thanks for that example. I am still concerned about the spreadsheet being password protected and that is there a way around linking or importing data to a database table even though the spreadsheet is protected, also using code like this "DoCmd.TransferSpreadsheet acImport," only inputs the first worksheet, how can I get around this and specify which worksheet I want to import as I have 5 sheets to import from about 20 alltogether.

Thanks,
Jez

Look at "DemoVidi1A2000" a new one.
Open Form1 and try. Not forget to RELINK EXCEL spreadshit, (4 excel link).

MStef-ZG, Thanks for the examples but its not really what I am looking for...

I have rethought the whole thing and have now got all the data into ranges on an excel spreadsheet. What I want to do now is using a form in my database, bring in each range and import it into a individual tables.

I have used this code below before to link a spreadsheet to a table which works fine but in this case I want only to import ranges of data as there are a number on the spreadsheet that all need importing to individual tables.

This is my code that have used in the past...

Private Sub cmdLoadData_Click()
   
    strPath = "\\titan\home\jeremyl1\"
    strFileName = Dir(strPath & "5HG_Feb_Comm.xls")
    strTempTable = "tbl18WeeksData"
    
    MsgBox "Data Loading...", vbInformation, "Jez"
    'If Len(strFileName) <> 0 Then
        DoCmd.TransferSpreadsheet acLink, , strTempTable, strPath & strFileName, True
    End If
    MsgBox "Import Complete", vbInformation, "Jez"
End Sub


How is this possible?
Jez

How much data is in your spreadsheet?

	Quote:
	
	
		
			
				Originally Posted by georgev
				
Why not just import all the spreadsheet data and then use a query within Access to only view the selection you want?
Depending on the size of the data of course.

I have 5 ranges of data (all named) and they are 56Cols X 20Rows.

all 5 range need to be imported to the relevant table (5 tables)

Jez

I asked for how much data was within your spreadsheet, if it's only 20 or so rows then why not just import the whole spreadsheet into a table and then query the "ranges" you need? Do you get what I mean?

I understand what you mean by import the whole worksheet but dont understand how to pick ranges of data once its in a table.

The ranges I want are all pretty much the same, they have same column headers and row headers, the only difference is the values under the column headers.

would this make any difference?

Jez

You can use a query to pick out the selection you want (assuming you can pick them out based on certain criteria (eg - Birth Date < '01/01/1990')

How would you identify the ranges using logic (other than, "thee 20 rows next ot eachother") - remember that computers are stupid - you have to tell them exactly what you want!


Related Links

Run Time Error 3035 System Resource Exceeded
my first db
Maximise
Combining Results from Mulitple Queries
Putting objects in OLE Object using java
Report Problem
Whoisloggedon - Saqib's tool
Completley Off Topic : Anyone seen Saila
Number of users in the database
looking for a snapshot guru
560mb of memory for this "group"???
Converting to DataAccessPage
strange update query ........
ADP, SQL7.0, access violation on oleaut32.dll/msado15.dll
ANDOR query PLEASE HELP
Turn $10 Into 10,000 In Just 14 Days With Paypal!!