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

 

[BuGlen]

The easiest way is to use the .UsedRange property of the worksheet object. You'll have to set a reference to the workbook and worksheet to use the property, but it should do what you need.

Hope this helps.

Glen Appleton

VB.Net student.

 

[vza]

I have decided to use the transferspreadsheet function from the Access library which is why I need to find the range dynamically....I do not think the function will accept the .UsedRange method of an Excel.Application object.

Thanks
-vza

 

[xlbo]

This MAY work - I emphasize the may as I have not actually tried it:

rsXL.Open "Select * From [Sheet1$A1:G" & rsXL.sheets(1).cells(65536,1).end(xlup).row & "]", CnXL, adOpenStatic

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

xlbo

I get an error stating that '[Sheet1$A1:G1]' is not a valid name....

-vza

 

[xlbo]

Would excel be open at this time ??

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

Here is what I have:

' Open Excel Workbook
   xlApp.Workbooks.Open FileName:=ConvertForm.ExcelText.Text
    
' Transfer Excel Spreadsheet to Access Database
    axApp.OpenCurrentDatabase ConvertForm.AccessText.Text
        
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TABLE1", ConvertForm.ExcelText.Text, True, "[Sheet1$A1:G" & xlApp.Sheets(1).Cells(65536, 1).End(xlUp).Row & "]"

    Set axApp = Nothing
    Set xlApp = Nothing

I don't think I am missing anything....

Thanks
-vza

 

[xlbo]

Try this:
"[Sheet1$A1:G" & xlApp.Activeworkbook.Sheets(1).Cells(65536, 1).End(xlUp).Row & "]"

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

same error....
Is there any way to store the worksheet's used range within a variable and then apply to the TransferSpreadsheet function??
Thanks for all the help..
Much appreciative!

-vza

 

[xlbo]

Set xlWB = xlApp.Workbooks.Open FileName:=ConvertForm.ExcelText.Text
with xlWB
lRow = .sheets("Sheet1").cells(65536,1).end(xlup).row
      .Names.Add Name:="myRange", RefersTo1:= _
        "=Sheet1!A1:G" & lRow
end with

' Transfer Excel Spreadsheet to Access Database
    axApp.OpenCurrentDatabase ConvertForm.AccessText.Text
        
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TABLE1", ConvertForm.ExcelText.Text, True, myRange

    Set axApp = Nothing
    Set xlApp = Nothing

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[combo]

Try to use in TransferSpreadsheet:
"Sheet1!A1:G" & xlApp.Sheets(1).Cells(65536, 1).End(xlUp).Row

combo

 

[xlbo]

oops - RefersTo1:= should read RefersTo:=

Rgds, Geoff
[blue]Si hoc signum legere potes, operis boni in rebus Latinus alacribus et fructuosis potiri potes![/blue]
Want the [red]best[/red] answers to your questions ? faq222-2244

 

[vza]

combo,

The command now runs...but nothing is transfrred...

xlbo,

I will try your alternative and report the results...Thanks!

-vza

 

[combo]

vza,
I've just noticed that you work with Application object instead of Workbook - this may produce wrong range.
So follow Geoff's line of code (which as a whole should work perfectly):
[tt]Set xlWB = xlApp.Workbooks.Open FileName:=ConvertForm.ExcelText.Text[/tt]
and use in TransferSpreadsheet:
"Sheet1!A1:G" & xlWb.Sheets(1).Cells(65536, 1).End(xlUp).Row

combo

 

[vza]

combo,
still no go...
The line executes but nothing is transferred...

this is what I have:

Private Sub ImportButton_Click()
 Dim axApp As New Access.Application
 Dim xlApp As New Excel.Application
 Dim xlWb As New Excel.Workbook

' Open Excel Workbook
   Set xlWb = xlApp.Workbooks.Open(FileName:=ConvertForm.ExcelText.Text)
    
' Transfer Excel Spreadsheet to Access Database
   axApp.OpenCurrentDatabase ConvertForm.AccessText.Text
        
   DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TABLE1", ConvertForm.ExcelText.Text, True, "Sheet1!A1:G" & xlWb.Sheets(1).Cells(65536, 1).End(xlUp).Row

   Set axApp = Nothing
   Set xlApp = Nothing

End Sub

This is very frustrating...thanks for helping as much as you guys have.

-vza

 

[combo]

Hi,
Could you try to dim workbook without "New":
[tt]Dim xlWb As Excel.Workbook[/tt]

With:
[tt]Dim axApp As New Access.Application[/tt]
the first reference to axApp creates new, hidden instance of access, so to work in it, use:
[tt]axApp.DoCmd.TransferSpreadsheet ...[/tt]
and:
[tt]axApp.Visible=True[/tt]

In case of problems, test the range address:
[tt]Msgbox "Sheet1!A1:G" & xlWb.Sheets(1).Cells(65536, 1).End(xlUp).Row[/tt]

So the code (without message):
[tt]Private Sub ImportButton_Click()
Dim axApp As New Access.Application
Dim xlApp As New Excel.Application
Dim xlWb As Excel.Workbook

' Open Excel Workbook
Set xlWb = xlApp.Workbooks.Open(FileName:=ConvertForm.ExcelText.Text)

' Transfer Excel Spreadsheet to Access Database
axApp.OpenCurrentDatabase ConvertForm.AccessText.Text
axApp.Visible=True
axApp.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, "TABLE1", ConvertForm.ExcelText.Text, True, "Sheet1!A1:G" & xlWb.Sheets(1).Cells(65536, 1).End(xlUp).Row

Set axApp = Nothing
Set xlApp = Nothing
Set xlWb=Nothing

End Sub[/tt]

Hope this will work.

combo

 

[vza]

The range is not working...
The MessageBox returns

[Sheet1!A1:G1]

even though the spreadsheet tested has over
50 entries....

Thanks
-vza

 

[BuGlen]

This function will return the used range of a worksheet using the .UsedRange object of the worksheet. It uses generic objects so it is not tied to any particular version of Excel library.

To use the function, use the following sample syntax:

Dim strRange As String

strRange = GetUsedRange("C:\MyPath\MyWorkbook.xls", "MySheetName")

-----

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
    
    Set xlSheet = Nothing
    
    xlBook.Close False
    Set xlBook = Nothing
    
    xlApp.Quit
    Set xlApp = Nothing

End Function

Hope this helps.

Glen Appleton

VB.Net student.

 

[VBAjedi]

Jumping in late here, but what about putting a dynamic named range into your worksheet that refers to the used range? Then you just have to refer to that named range in your code, which should be easy.

General pointers on this can be found here:

http://www.beyondtechnology.com/geeks007.shtml

although it will need a bit of tweaking.

Just wanted to provide an alternate suggestion. . .

VBAjedi

 

[VBAjedi]

Doh! That assumes that the file you are opening is an existing Excel file, which a second read of your posts indicates may not be the case. . .

VBAjedi