Prev: opening a 2007 edited file in 2003 shows blank screen
Next: NÃO CONSIGO DELETAR ESSA MENSAGEM!!
From: Ayo on 2 Jul 2008 16:21
I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
on a workbook with three sheets, each with different data configuration.

Function GetExcelFileData(tableName As String, filepath As String)
Dim lglastRow As Long, lglastColumn As Long
lglastRow = Range("A65536").End(xlUp).Row
lglastColumn = Range("A65536").End(xlToRight).Column

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True
End Function

From: Klatuu on 2 Jul 2008 16:40
There is a Range argument after the HasFieldNames argument where you can
identitfy the sheet name and if you want the the Cell Range. To get 3
different sheets, you will need to call the transfer 3 times, once for each
sheet.
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetOne$A:Q"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetTwo"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, "SheetThree"




--
Dave Hargis, Microsoft Access MVP


"Ayo" wrote:

> I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
> on a workbook with three sheets, each with different data configuration.
>
> Function GetExcelFileData(tableName As String, filepath As String)
> Dim lglastRow As Long, lglastColumn As Long
> lglastRow = Range("A65536").End(xlUp).Row
> lglastColumn = Range("A65536").End(xlToRight).Column
>
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> filepath, True
> End Function
>
From: Ayo on 2 Jul 2008 16:55
how do I combine the sheetname with the cell range using something like this:
lglastRow = Range("A65536").End(xlUp).Row
lglastColumn = Range("A65536").End(xlToRight).Column

can I use something like this for my range :
wks.Range(cell(2, 1), cell(lglastRow, lglastColumn))

"Klatuu" wrote:

> There is a Range argument after the HasFieldNames argument where you can
> identitfy the sheet name and if you want the the Cell Range. To get 3
> different sheets, you will need to call the transfer 3 times, once for each
> sheet.
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> filepath, True, "SheetOne$A:Q"
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> filepath, True, "SheetTwo"
> DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> filepath, True, "SheetThree"
>
>
>
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Ayo" wrote:
>
> > I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
> > on a workbook with three sheets, each with different data configuration.
> >
> > Function GetExcelFileData(tableName As String, filepath As String)
> > Dim lglastRow As Long, lglastColumn As Long
> > lglastRow = Range("A65536").End(xlUp).Row
> > lglastColumn = Range("A65536").End(xlToRight).Column
> >
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > filepath, True
> > End Function
> >
From: Klatuu on 2 Jul 2008 17:00
No, the code you are trying to use is valid only in Excel. If you need to do
that in Access, you will have to use Automation to open the workbook and
select the sheet.

When importing from Excel using the TransferSpreadsheet, you don't need to
tell it the last row or column. It will import all contiguous rows and
columns. Now, if you have a blank row, it will not import anything below the
blank row. The same applies to columns.

--
Dave Hargis, Microsoft Access MVP


"Ayo" wrote:

> how do I combine the sheetname with the cell range using something like this:
> lglastRow = Range("A65536").End(xlUp).Row
> lglastColumn = Range("A65536").End(xlToRight).Column
>
> can I use something like this for my range :
> wks.Range(cell(2, 1), cell(lglastRow, lglastColumn))
>
> "Klatuu" wrote:
>
> > There is a Range argument after the HasFieldNames argument where you can
> > identitfy the sheet name and if you want the the Cell Range. To get 3
> > different sheets, you will need to call the transfer 3 times, once for each
> > sheet.
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > filepath, True, "SheetOne$A:Q"
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > filepath, True, "SheetTwo"
> > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > filepath, True, "SheetThree"
> >
> >
> >
> >
> > --
> > Dave Hargis, Microsoft Access MVP
> >
> >
> > "Ayo" wrote:
> >
> > > I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
> > > on a workbook with three sheets, each with different data configuration.
> > >
> > > Function GetExcelFileData(tableName As String, filepath As String)
> > > Dim lglastRow As Long, lglastColumn As Long
> > > lglastRow = Range("A65536").End(xlUp).Row
> > > lglastColumn = Range("A65536").End(xlToRight).Column
> > >
> > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > > filepath, True
> > > End Function
> > >
From: Ayo on 2 Jul 2008 20:47
Hi again,
Now I am really stuck. This is what I have so far:

Private Sub cmdImport_Update_Click()
On Error Resume Next

Dim appExcel As Excel.Application, wbk As Excel.Workbook, wks As
Excel.Worksheet
Dim strMsg As String, strFile As String, strTrackerFile As String,
strshName As String
Dim lglastRow As Long, lglastColumn As Long
Dim bytWks As Byte, bytMaxPages As Byte
Dim todays_Date
Dim cell As Range
Dim strfilePath As String, strDbTable As String

todays_Date = Format(Date, "mmmdd_yyyy")
strfilePath = CurrentProject.Path & "\NJ RFDS Tracker" & todays_Date &
".xls"
Me.lblMsg.Caption = "Ready for Import Operation."
bytMaxPages = 3

' Create the Excel Applicaiton, Workbook and Worksheet
Set appExcel = Excel.Application
Set wbk = appExcel.Workbooks.Open(strfilePath)

CurrentDb.Execute "DELETE FROM [tbl_SiteInformation_Import]"
CurrentDb.Execute "DELETE FROM [tbl_SiteConfiguration_Import]"

For bytWks = 1 To bytMaxPages
Set wks = appExcel.Worksheets(bytWks)
'lglastRow = wks.Range("A65536").End(xlUp).Row
'lglastColumn = wks.Range("A65536").End(xlToRight).Column
'strshName = wks. 'Range(cell(2, 1), cell(lglastRow,
lglastColumn))

Select Case bytWks
Case 1
strDbTable = "tbl_SiteInformation_Import"
Case 2, 3
strDbTable = "tbl_SiteConfiguration_Import"
End Select
GetExcelFileData strDbTable, strfilePath, wks
Next bytWks

Set wks = Nothing
wbk.Close True
Set wbk = Nothing
appExcel.Quit
Set appExcel = Nothing

End Sub

Function GetExcelFileData(tableName As String, filepath As String, shName As
Excel.Worksheet)
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
filepath, True, shName
End Function

And here is the error I am getting: An expression you entered is the wrong
data type for one of the arguments. I know it is the range argument, I am
have a problem with how to use it. Any help will be greatly appreciated.

Thanks again.

"Klatuu" wrote:

> No, the code you are trying to use is valid only in Excel. If you need to do
> that in Access, you will have to use Automation to open the workbook and
> select the sheet.
>
> When importing from Excel using the TransferSpreadsheet, you don't need to
> tell it the last row or column. It will import all contiguous rows and
> columns. Now, if you have a blank row, it will not import anything below the
> blank row. The same applies to columns.
>
> --
> Dave Hargis, Microsoft Access MVP
>
>
> "Ayo" wrote:
>
> > how do I combine the sheetname with the cell range using something like this:
> > lglastRow = Range("A65536").End(xlUp).Row
> > lglastColumn = Range("A65536").End(xlToRight).Column
> >
> > can I use something like this for my range :
> > wks.Range(cell(2, 1), cell(lglastRow, lglastColumn))
> >
> > "Klatuu" wrote:
> >
> > > There is a Range argument after the HasFieldNames argument where you can
> > > identitfy the sheet name and if you want the the Cell Range. To get 3
> > > different sheets, you will need to call the transfer 3 times, once for each
> > > sheet.
> > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > > filepath, True, "SheetOne$A:Q"
> > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > > filepath, True, "SheetTwo"
> > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > > filepath, True, "SheetThree"
> > >
> > >
> > >
> > >
> > > --
> > > Dave Hargis, Microsoft Access MVP
> > >
> > >
> > > "Ayo" wrote:
> > >
> > > > I need to figure out a way to run the " DoCmd.TransferSpreadsheet acImport"
> > > > on a workbook with three sheets, each with different data configuration.
> > > >
> > > > Function GetExcelFileData(tableName As String, filepath As String)
> > > > Dim lglastRow As Long, lglastColumn As Long
> > > > lglastRow = Range("A65536").End(xlUp).Row
> > > > lglastColumn = Range("A65536").End(xlToRight).Column
> > > >
> > > > DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, tableName,
> > > > filepath, True
> > > > End Function
> > > >
 |  Next  |  Last
Pages: 1 2
Prev: opening a 2007 edited file in 2003 shows blank screen
Next: NÃO CONSIGO DELETAR ESSA MENSAGEM!!