By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,509 Members | 1,686 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,509 IT Pros & Developers. It's quick & easy.

Importing specific worksheet ranges

P: n/a
All

I'm currently attempting to move us from a spreadsheet based system to
a database system. The first phase is to import the data on a regular
basis from the spreadsheets to get the database working the way we need
it to prior to switching users over to it.

Each team has a workbook, each workbook contains multiple worksheets,
only some of the sheets contain the ranges that I want to import.

Currently I can't seem to get the import to move from one sheet to
another, the code below just sticks me in a loop, even though the
worksheet name comes up it still seems to bear no relation to the
worksheet that it decides to import, it just brings the first one in
the workbook in each time.

Function Get_stages_status()

On Error GoTo Err_Handler
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim strPath As String
Dim CSCI_Name As Variant
Dim Workbook_Array As Variant
Dim rowcount As Variant
Dim startcol As Variant
Dim endcol As Variant

'Dim Workbook_Array1 As Variant
Dim intI As Variant
Dim intJ As Variant

Dim Current_DB As Database
Dim rst As Recordset
Dim rst_update As Recordset
Dim range As Variant
Set Current_DB = DBEngine.Workspaces(0).Databases(0)
Set rst = Current_DB.OpenRecordset("Temp_Data_Stages")
Set rst_update = Current_DB.OpenRecordset("Temp_Stages_All")
startcol = "B"
endcol = "R"
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_Stages" 'Clear table
DoCmd.RunSQL "delete * from Temp_Stages_All" 'Clear table

Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\FDP CTR iFACTS.xls")

For Each intI In Workbook_Array

strPath = intI

'Establish workbook string
Set xlBook = xlApp.Workbooks.Open(strPath, False, True)

'Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Worksheets
On Error Resume Next
rowcount = 14
If xlSheet.Name = "AOD0477_EV<FDP>" Then
MsgBox "woo hoo" & xlSheet.Name
MsgBox strPath
Do

range = startcol & rowcount & ":" & endcol & rowcount

'Transfer the data from the worksheet
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
"Temp_Data_Stages", _
strPath, _
False, _
range
Set rst = Current_DB.OpenRecordset("Temp_Data_Stages")

'I've cut all this out while I try to work out what is going
on, basically I end up in an endless loop, control break out of it and
find that I have a list of repeating rows from the first worksheet,
which is not AOD0477_EV<FDP>". How can I control what worksheet it
gets data from?

'Update recordset with data
'rst.MoveFirst

'Do
'rst_update.AddNew
'rst_update!CSCI = "FDP"
'rst_update!Paper = rst!F1.Value
'rst_update!Version = rst!F2.Value
'rst_update!SLOC = rst!F13.Value
'rst_update!Hours = rst!F14.Value
'rst_update!conf = rst!F13.Value
'rst_update.Update

'rst.MoveNext

'Loop While Not rst.EOF
rowcount = rowcount + 1

Loop While rowcount < 52
End If

'DoCmd.RunSQL "delete * from temp_data_Estimate" 'Clear table
'End If
On Error Resume Next

Next
MsgBox "Complete " & strPath

Next

xlBook.Application.Quit

'Delete data that does not apply
'DoCmd.OpenQuery "Delete_blanks"

'Error Handler used to catch error when the fields are blank
Exit Function

Exit_Handler:
On Error Resume Next

Err_Handler:
On Error Resume Next
End Function

Jul 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Not to worry I've sorted it now.
ma****************@lmco.com wrote:
All

I'm currently attempting to move us from a spreadsheet based system to
a database system. The first phase is to import the data on a regular
basis from the spreadsheets to get the database working the way we need
it to prior to switching users over to it.

Each team has a workbook, each workbook contains multiple worksheets,
only some of the sheets contain the ranges that I want to import.

Currently I can't seem to get the import to move from one sheet to
another, the code below just sticks me in a loop, even though the
worksheet name comes up it still seems to bear no relation to the
worksheet that it decides to import, it just brings the first one in
the workbook in each time.

Function Get_stages_status()

On Error GoTo Err_Handler
Dim xlApp As Object ' Excel.Application
Dim xlBook As Object ' Excel.Workbook
Dim xlSheet As Object ' Excel.Worksheet
Dim strPath As String
Dim CSCI_Name As Variant
Dim Workbook_Array As Variant
Dim rowcount As Variant
Dim startcol As Variant
Dim endcol As Variant

'Dim Workbook_Array1 As Variant
Dim intI As Variant
Dim intJ As Variant

Dim Current_DB As Database
Dim rst As Recordset
Dim rst_update As Recordset
Dim range As Variant
Set Current_DB = DBEngine.Workspaces(0).Databases(0)
Set rst = Current_DB.OpenRecordset("Temp_Data_Stages")
Set rst_update = Current_DB.OpenRecordset("Temp_Stages_All")
startcol = "B"
endcol = "R"
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_Stages" 'Clear table
DoCmd.RunSQL "delete * from Temp_Stages_All" 'Clear table

Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\FDP CTR iFACTS.xls")

For Each intI In Workbook_Array

strPath = intI

'Establish workbook string
Set xlBook = xlApp.Workbooks.Open(strPath, False, True)

'Loop through all the Sheets in the workbook
For Each xlSheet In xlBook.Worksheets
On Error Resume Next
rowcount = 14
If xlSheet.Name = "AOD0477_EV<FDP>" Then
MsgBox "woo hoo" & xlSheet.Name
MsgBox strPath
Do

range = startcol & rowcount & ":" & endcol & rowcount

'Transfer the data from the worksheet
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
"Temp_Data_Stages", _
strPath, _
False, _
range
Set rst = Current_DB.OpenRecordset("Temp_Data_Stages")

'I've cut all this out while I try to work out what is going
on, basically I end up in an endless loop, control break out of it and
find that I have a list of repeating rows from the first worksheet,
which is not AOD0477_EV<FDP>". How can I control what worksheet it
gets data from?

'Update recordset with data
'rst.MoveFirst

'Do
'rst_update.AddNew
'rst_update!CSCI = "FDP"
'rst_update!Paper = rst!F1.Value
'rst_update!Version = rst!F2.Value
'rst_update!SLOC = rst!F13.Value
'rst_update!Hours = rst!F14.Value
'rst_update!conf = rst!F13.Value
'rst_update.Update

'rst.MoveNext

'Loop While Not rst.EOF
rowcount = rowcount + 1

Loop While rowcount < 52
End If

'DoCmd.RunSQL "delete * from temp_data_Estimate" 'Clear table
'End If
On Error Resume Next

Next
MsgBox "Complete " & strPath

Next

xlBook.Application.Quit

'Delete data that does not apply
'DoCmd.OpenQuery "Delete_blanks"

'Error Handler used to catch error when the fields are blank
Exit Function

Exit_Handler:
On Error Resume Next

Err_Handler:
On Error Resume Next
End Function
Jul 10 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.