Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Excel VBA > Excel VBA
|
Excel VBA Discuss using VBA for Excel programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Excel VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old March 20th, 2007, 10:16 AM
Registered User
 
Join Date: Mar 2007
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Importing from Excel to Access table

I have a spreadsheet that contains work schedule information. Employees name's are in column A, in columns b-AF days of the month.I need to take this information and out in a table in access. The table consist of First name, last name, date worked. If I import using the File/get external data I get the column headings as field names such as 3/19/07 and 3/20.

I need for the program to write the data as:

rec#1
Mike
Jones
3/19
Rec#2
Mike
Jones
3/20
etc.

Each date is a new record. I hope this makes some sense.

Thanks!

 
Old March 21st, 2007, 12:50 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

The best way to do this would be to open the spreadsheet in Access and have it process each line.
In MS Access click Tools > References and add "Microsoft Excel 11.0 Object Library" by checking it.

Now you can open excel files and reference them just as you would in Excel by opening them like this:
-------------------------------------------------------------------------------------------
  Dim oWorkbook As Excel.Application, oSource As Worksheet
  Set oWorkbook = CreateObject(Class:="Excel.Application")
  oWorkbook.Visible = False 'Set to true if you want to see the opened workbook being processed
  oWorkbook.Workbooks.Open "c:\my.xls"
  Set oSource = oWorkbook.ActiveWorkbook.ActiveSheet
  ~~~Your Handling Code Here, ie rsMyRecordset("This Field") = oSource.cells(iRowOn,iColumnOn)~~~
  oWorkbook.ActiveWorkbook.Close SaveChanges:=False 'Set to true changes were made that you want to save
  oWorkbook.Quit
-------------------------------------------------------------------------------------------
You can read or write to the workbook as you would from within Excel.

This assumes you know how to add records to the database via code already and how to handle looping through spreadsheet data to extract what you want. Probably a set of nested do/while loops would be best.

Hope this helps.

 
Old March 21st, 2007, 12:52 PM
Friend of Wrox
 
Join Date: Feb 2007
Posts: 163
Thanks: 0
Thanked 2 Times in 2 Posts
Default

You can also open the recordset from within Excel but requires a connection string. I didn't show this because I'm not aware of any of the information about the database necessary to build it for you.

 
Old April 3rd, 2007, 03:21 AM
Registered User
 
Join Date: Apr 2007
Posts: 4
Thanks: 0
Thanked 0 Times in 0 Posts
Send a message via Yahoo to zizo2120
Default

thanks for help allenm





Similar Threads
Thread Thread Starter Forum Replies Last Post
importing excel data into access forms navdeepsinghparmar Access 1 November 7th, 2007 01:27 PM
Importing Excel sheet into Access using ASP prasanta2expert ASP.NET 1.0 and 1.1 Basics 1 October 5th, 2006 02:36 PM
Importing Hyperlinks into Access table Sammy8932 Access VBA 0 May 11th, 2005 09:23 AM
Importing text data to an access table sbyers_1982 VBScript 4 November 29th, 2004 01:57 PM
Export Table from Access to Excel aramchan Access VBA 2 July 12th, 2004 03:20 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.