Excel Worksheet Range 3

[vza]

Quick question...Say I have a line of code like the following:

rsXL.Open "Select * From [Sheet1$A1:G100]", CnXL, adOpenStatic

If I don't know the exact range of the worksheet i needed during runtime is there any way I could represent it in code??

Any responses would be greatly appreciated.

Thanks
-vza

 

[TheBitDoctor]

vza:

What happens when you leave the range parameter blank which would ask that the entire worksheet be imported?

What is the layout of your spreadsheet? Are there entries in column A for each record? What happens if you do your search for the last record on column G instead of column A:

"Sheet1!A1:G" & xlWb.Sheets(1).Cells(65536, 7).End(xlUp).Row

You should probably search on the key field since your only looking for a record count anyway.

If your range formula results in Sheet1!A1:G1 and you've indicated that the first row contains column headers then you wont get any data transferred because there are no data records within the specified range.

Ron

 

[vza]

BuGlen,

With a few alterations to your function I was able to get the DB to transfer the appropriate range of the Excel spreadsheet....The TransferSpreadsheet Range argument is very picky...it must be in this exact form (within a string variable):

Sheet1!A1:B1

So what I did is convert the used range string:

$A$1:$B$1

to resemble the previous string format and the data was transferred with no problems....only now the records are transferred out of order...I am not sure why this happens but it does. I need to figure out how to stop this from happening for the order of the spreadsheet entries has relevance....

Other than that...its seems to be working nicely!
I appreciate all the help from everyone...I couldnt have even got this far without the assistance Here is what I did...hopefully this will alleviate future headaches:

Function GetUsedRange(WorkbookPath As String, SheetName As String) As String

    Dim xlApp As Object
    Dim xlBook As Object
    Dim xlSheet As Object

    Set xlApp = CreateObject("Excel.Application")
    
    Set xlBook = xlApp.Workbooks.Open(WorkbookPath)
    Set xlSheet = xlBook.Sheets(SheetName)
    
    GetUsedRange = xlSheet.UsedRange.Address
    Debug.Print GetUsedRange
    GetUsedRange = Replace(GetUsedRange, "$", "")
    GetUsedRange = "Sheet1!" & GetUsedRange
    Debug.Print GetUsedRange
    
    Set xlSheet = Nothing
    
    xlBook.Close False
    Set xlBook = Nothing
    
    xlApp.Quit
    Set xlApp = Nothing

End Function

None of the spreadsheet entries are numbered....why would they be sorted in a different order than in the spreadsheet?? Shouldn't The function just transfer the spreasdsheet by row??

Thanks
-vza

 

[vza]

I think everything is alright now....
It seems to be copying everything in order...I tried a few different spreadsheets with good results...


Once again...Thanks to all who have helped.

-vza