 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Importing / Linking / August 2005Active Spreadsheets.
is it possible to find out (Before import) what datasheets in an excel file contain data. In addition can you find the first active coloum & row also the last coloum & row of an active sheet It depends what you mean by "data" and "active". In general you can do something like this air code, which relies on Excel's UsedRange property: Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet Set oBook = GetObject("C:\Folder\File.xls") For Each oSheet in oBook.Worksheets With oSheet Debug.Print .Name, .UsedRange.Address, _ .UsedRange.Cells(1,1).Formula End With Next Set oSheet = Nothing oBook.Close False Set oBook = Nothing A sheet whose UsedRange is $A$1 and with no data in $A$1 is empty. However, Excel's idea of a UsedRange isn't always the same as yours or mine. You may need to run code to reset the UsedRange on each sheet before you use it: see http://www.mvps.org/dmcritchie/excel/lastcell.htm#resetall for ideas. >is it possible to find out (Before import) what datasheets in an excel file >contain data. > >In addition can you find the first active coloum & row > >also the last coloum & row of an active sheet -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. John I am working on access 2000 & it does not like dim obook as excel.workwork Urgent Help pls > It depends what you mean by "data" and "active". In general you can do > something like this air code, which relies on Excel's UsedRange [quoted text clipped - 36 lines] > > Please respond in the newgroup and not by email. Thanks John. but my database does not like "dim obook as excel.workbook" can u help please urgently > It depends what you mean by "data" and "active". In general you can do > something like this air code, which relies on Excel's UsedRange [quoted text clipped - 36 lines] > > Please respond in the newgroup and not by email. You need to set a reference (Tools|References) to the Microsoft Excel X Object Library (the value of X depends on the version of Office you are using. > Thanks John. > [quoted text clipped - 41 lines] >> >> Please respond in the newgroup and not by email. There doesn't appear to be anything in your code that requires Early Binding, John. Why not simply change Dim oBook As Excel.Workbook Dim oSheet As Excel.Worksheet to Dim oBook As Object Dim oSheet As Object and use it without a reference? Ok, I'll admit it: I haven't tested. I can't guarantee that Set oBook = GetObject("C:\Folder\File.xls") will work. If it doesn't, though, it's simple to add another Dim xlApp As Object and use Set xlApp = CreateObject("Excel.Application") Set oBook = xlApp.Workbooks.Open(strFile)  SignatureDoug Steele, Microsoft Access MVP http://I.Am/DougSteele (no e-mails, please!) > You need to set a reference (Tools|References) to the Microsoft Excel X > Object Library (the value of X depends on the version of Office you are [quoted text clipped - 45 lines] >>> >>> Please respond in the newgroup and not by email. Hi Doug, Intellisense is the only reason. >There doesn't appear to be anything in your code that requires Early >Binding, John. Why not simply change [quoted text clipped - 19 lines] > Set xlApp = CreateObject("Excel.Application") > Set oBook = xlApp.Workbooks.Open(strFile) -- John Nurick [Microsoft Access MVP] Please respond in the newgroup and not by email. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.