Microsoft Access Development
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
 
User Name:
Password:
Remember me
 



Go Back   Dev Articles Community ForumsDatabasesMicrosoft Access Development

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Display Modes
 
Unread Dev Articles Community Forums Sponsor:
  #1  
Old July 8th, 2005, 09:32 AM
summer brew summer brew is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2005
Posts: 12 summer brew User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0
Importing an Excel file into Access via VBA

I have found a ton of information on how to import an Access table into Excel, but I have come up short when I have been looking for a way to import an Excel file into Access via VBA.

Currently, I have 3 tables that I update monthly manually by means of the Import Wizard, but I am looking to automate the process as much as possible.

At the least, I would like to hit a button in a form that imports a particular Excel file (the name doesn't change from month to month) and at least brings me into the import wizard.

I am just starting out in VBA, and I know this is going to require a bit of code, so I'm reaching out for HELP!

Big thanks in advance for helping me out...

Paul

Reply With Quote
  #2  
Old July 8th, 2005, 10:08 AM
sherrington sherrington is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Aug 2004
Posts: 110 sherrington User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 13 h 30 m 33 sec
Reputation Power: 10
try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportSpecification", "FileName", True

Reply With Quote
  #3  
Old July 8th, 2005, 11:05 AM
summer brew summer brew is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2005
Posts: 12 summer brew User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 51 m 8 sec
Reputation Power: 0
I tried the TransferSpreadsheet Method, and it works, but only if the Excel file is open. If the Excel file isn't open I get an error message: "External table isn't in the expected format".

Any insight?

Reply With Quote
  #4  
Old July 11th, 2005, 01:30 PM
lwells lwells is offline
Contributing User
Dev Articles Novice (500 - 999 posts)
 
Join Date: Sep 2004
Posts: 632 lwells User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 21 h 59 m 38 sec
Reputation Power: 10
Try checking which version of Excel you are using and change the acSpreadsheetTypeExcel9 to that version of Excel and see if that doesn't solve the problem.

lwells

Reply With Quote
  #5  
Old July 19th, 2005, 05:07 PM
br_esper br_esper is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Jul 2005
Posts: 23 br_esper User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 10 h 55 m 14 sec
Reputation Power: 0
Import XLS using RecordSet

Dim rs2 As New ADODB.Recordset
Dim cnn2 As New ADODB.Connection
Dim cmd2 As New ADODB.Command

With cnn2
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "Data Source="c:\temp.xls;" & "Extended Properties=Excel 8.0;"
.Open
End With

Set cmd2.ActiveConnection = cnn2
cmd2.CommandType = adCmdText
cmd2.CommandText = "SELECT * FROM [Sheet1$] Where [Date] IS NOT NULL AND [Expense ID] IS NOT NULL "
rs2.CursorLocation = adUseClient
rs2.CursorType = adOpenStatic
rs2.LockType = adLockReadOnly
rs2.Open cmd2

While Not rs2.EOF
...do stuff...
rs2.MoveNext
Wend

Reply With Quote
  #6  
Old May 3rd, 2006, 12:48 PM
Anand Khinvasar Anand Khinvasar is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: May 2006
Posts: 1 Anand Khinvasar User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 14 m 48 sec
Reputation Power: 0
A simple solution

Check following things:

1) If you have generated excel sheets in older version at some location (say DoCmd.OutputTo command which generates excel sheet in Excel3 format) and then tried generating excel files in newer version with the same name at same location(say Docmd.TransferSpeadsheet method which allows variety of Excel formats like Excel97). Solution for this:
Delete all the older excel sheets at the specified location and try again.

2) Try changing datatypes. Excel3 format of Docmd.OutputTo allows text field of maximum 255 characters. So to allow more characters use Docmd.Spreadsheet method. Also linked tables should have there field format set to memo instead of text. (On SQL Server set the same field to text(16))

Reply With Quote
  #7  
Old February 15th, 2010, 12:03 PM
erekh erekh is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2010
Posts: 2 erekh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 44 sec
Reputation Power: 0
Inserting header into excel column

I'm attempting to import a workbook (need data from multiple tabs) and that seems to be going ok, except for the fact that I need a heading added to one of my columns. Is there a way to do this in vba as the workbook is systematically generated?


Quote:
Originally Posted by sherrington
try

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "ImportSpecification", "FileName", True

Reply With Quote
  #8  
Old February 15th, 2010, 02:10 PM
AlanSidman's Avatar
AlanSidman AlanSidman is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2008
Location: Lake County, IL
Posts: 240 AlanSidman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 15 h 49 m 19 sec
Reputation Power: 6
Why doesn't your excel spreadsheet have a heading already? Access imports the headings from Excel if you set the first row to contains headings.

Alan
__________________
Alan Sidman
__________
If I helped you, I'd like to know. Click on the icon next to the thread number and tell me.

Reply With Quote
  #9  
Old February 15th, 2010, 04:51 PM
erekh erekh is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Feb 2010
Posts: 2 erekh User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 6 m 44 sec
Reputation Power: 0
Magic of our IT team I work with and just to get it added I would have to go through 'change control' get funding, have meetings, and waste a ton of time and resources for something I would think would be doable as long as someone out there knows the code... There are two columns on the specific worksheet i'm dealing with.
The first column does have text (which i use as a heading, even though it is more like a title) but the second column just has the values...




Quote:
Originally Posted by AlanSidman
Why doesn't your excel spreadsheet have a heading already? Access imports the headings from Excel if you set the first row to contains headings.

Alan

Reply With Quote
  #10  
Old February 16th, 2010, 09:34 AM
AlanSidman's Avatar
AlanSidman AlanSidman is offline
Contributing User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2008
Location: Lake County, IL
Posts: 240 AlanSidman User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 1 Day 15 h 49 m 19 sec
Reputation Power: 6
I still don't understand why you can't open the excel spreadsheet before importing it into Access and simply type in the heading you desire. Failing this, Access will import the data and will put a fieldname called Field1 if there is no name. You can then go into the table properties and change the name to what ever you would like. It appears to me like you are trying to over-engineer this project. A second alternative is to have it import the the data into an existing table which already has the field names assigned.

Alan

Reply With Quote
  #11  
Old November 27th, 2012, 08:04 AM
TudX TudX is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Location: Brazil
Posts: 1 TudX User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 18 m 24 sec
Reputation Power: 0
Send a message via ICQ to TudX
Solution for Access 2010

By now you have probably solved your problem, but as this is one of the first results in my Google search, I'll post my solution, that might be usefull to someone:

Code:
Sub ImportXLS()
Dim db As DAO.Database
Set db = CurrentDb
On Error Resume Next:   db.TableDefs.Delete "tblImport":   On Error GoTo 0
db.TableDefs.Refresh
DoCmd.TransferSpreadsheet _
    TransferType:=acImport, _
    SpreadsheetType:=acSpreadsheetTypeExcel9, _
    TableName:="tblImport", _
    FileName:="C:\FileName.xls", _
    HasFieldNames:=True, _
    Range:="SheetName!A:DN"
db.TableDefs.Refresh
db.Close:   Set db = Nothing
End Sub


If you want help on the TransferSpreadsheet function, put the cursor over it and press F1.

If you receive a "not defined user-defined data type" error, you need to reference the DAO Library. Check http://support.microsoft.com/kb/145759 for the solution.

Good luck!

Reply With Quote
  #12  
Old November 27th, 2012, 08:05 PM
Serena5233 Serena5233 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Nov 2012
Posts: 4 Serena5233 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 15 m 9 sec
Reputation Power: 0
-----------------------------------------
URL

Reply With Quote
  #13  
Old December 3rd, 2012, 12:26 AM
stevarbour3 stevarbour3 is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 7 stevarbour3 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 14 m 44 sec
Reputation Power: 0
If you want help on the TransferSpreadsheet function, put the cursor over it and press F1.

Reply With Quote
  #14  
Old December 3rd, 2012, 06:46 AM
Beve3rly Beve3rly is offline
Registered User
Dev Articles Newbie (0 - 499 posts)
 
Join Date: Dec 2012
Posts: 5 Beve3rly User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 9 m 34 sec
Reputation Power: 0
I would like to hit a button in a form that imports a particular Excel file


Reply With Quote
Reply

Viewing: Dev Articles Community ForumsDatabasesMicrosoft Access Development > Importing an Excel file into Access via VBA


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.

© 2003-2014 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap