Database Management
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me
Go Back   Dev Shed Forums > Databases > Database Management
Reload this Page

Excel interchanging data with access

Discuss Excel interchanging data with access in the Database Management forum on Dev Shed.
Excel interchanging data with access Database Management forum discussing non-database specific SQL. Structured Query Language was designed to be a robust and standardized language for manipulating relational databases.

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
« Previous Thread | Next Thread »  
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
Stay one step ahead of the competition. Evaluate and give feedback on some of the hottest web development tools on the market today. Make your opinion heard! Click Here
  #1  
Old March 6th, 2003, 12:10 AM
biaz's Avatar
biaz biaz is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 14 biaz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Excel interchanging data with access
Hey

I have excel book with many sheets I need to import from to create access tables, the book being an unchangeable form. Mostly.

I was planning to create macro for importing the data shellwise. But how can I check that I'm getting it from the correct row (in case someone has inserted another row)? For that I should check the content of the first cell of the row to see it has the correct value in it (for example 1.1.4 or 2.1). How should I do that? Would it then be better to implement the importing some other way than macro? (Note that this is the first time I'm creating db)

Another puzzle... I have to create function chart, not barchart. How can I do that in access? I wondered if I really have to get the data back in excel. Is there a possibility to create goodlooking charts in msa whereas I might not have the possibility to even use pivot tables. Or, if I export data, can I control excel right the same way to create specific type of graph in an instant?

Thank you very much for your brainwork

In addition:
I'd be glad if I could write the control into code resemblling following (found at mvps.org)

DoCmd.TransferSpreadsheet transfertype:=acImport, SpreadsheetType:=5, _
tablename:="tmpTableName", FileName:="SomeExcelFile", _
Hasfieldnames:=False, Range:="WorkSheet!B1:B11"
'This will import the range B1 through B11
'The Spreadsheet type = 5 specifies an Excel 5.0/7.0 file
'format
Last edited by biaz : March 6th, 2003 at 03:57 AM.
Reply With Quote
  #2  
Old March 6th, 2003, 07:51 AM
FreddieB FreddieB is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Arlington,Texas
Posts: 14 FreddieB User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Question
Can you link your spread sheet to a table in Access? If you do you could link your tables in access to the workbook and the data in your database would change when any changes in your work book accure.
Reply With Quote
  #3  
Old March 7th, 2003, 01:25 PM
biaz's Avatar
biaz biaz is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 14 biaz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
No can do, several different persons and locations will be handling this data, but reasonable suggestion otherwise Probably will pass that one.... wouldnt want to write whole program to handle only one feat. IŽll have the questionable joy of restricting people whoŽll have to work with it...sigh.
Reply With Quote
  #4  
Old March 17th, 2003, 10:40 PM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Wink Half your answer
As for macro programming to search through looking for results, you can use something like this

PHP Code:
 Sub Search_For_Model(Model)
    
    Call Find_Last_Row
    
    Dim ModelFound As Boolean
    
    ModelRowNumber = 1
    ModelFound = False
    Do
        Do While ModelFound = False
            ModelRowNumber = ModelRowNumber + 1
            If Worksheets("Dbase").Cells(ModelRowNumber, 3) = Model Then
                ModelFound = True
                Exit Do
            ElseIf ModelRowNumber = LastRowNumber Then
                GoTo ModelNotFound
            End If
        Loop
    Loop Until ModelFound = True
        
    Exit Sub

ModelNotFound:
    MsgBox ("Model: " & Model & ", can not be found")
End Sub


Sub Find_Last_Row()
    Dim LastRow As Boolean
    Dim RowCount
    RowCount = 0
    
    Do
        Do While LastRow = False
            RowCount = RowCount + 1
            If Worksheets("DBase").Cells(RowCount, 1) = "" Then
                LastRow = True
                Exit Do
            End If
        Loop
    Loop Until LastRow = True

    'LastRowNumber is a global variable
    LastRowNumber = RowCount
End Sub 


Hope this helps you out - see next post for code to go through all worksheets in a workbook.
__________________
How can I soar like an eagle when
I'm flying with turkey's?
Last edited by mohecan : March 17th, 2003 at 10:45 PM.
Reply With Quote
  #5  
Old March 17th, 2003, 10:44 PM
mohecan mohecan is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Feb 2003
Location: Melbourne, Australia
Posts: 212 mohecan User rank is Private First Class (20 - 50 Reputation Level)mohecan User rank is Private First Class (20 - 50 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 6
Post Loop through all worksheets in a workbook
Here's code to go through all worksheets in a workbook.

PHP Code:
 Sub LoopThroughAllSheets()

    Dim i
    
    For i = 1 To Worksheets.Count
        'Your actions for each sheet go here
    Next i

End Sub 
Reply With Quote
  #6  
Old March 18th, 2003, 03:55 AM
biaz's Avatar
biaz biaz is offline
Junior Member
Dev Shed Newbie (0 - 499 posts)
  
Join Date: Jan 2003
Posts: 14 biaz User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: < 1 sec
Reputation Power: 0
Thank you very much. This I will definetly try out.
Reply With Quote
Reply

Viewing: Dev Shed Forums > Databases > Database Management > Excel interchanging data with access

« Previous Thread | Next Thread »

Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Linear Mode Linear Mode
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 | 
  
 





© 2003-2008 by Developer Shed. All rights reserved. DS Cluster 3 hosted by Hostway.