I'm hoping the answer to this is that I'm just doing something silly,
but I'm really scratching my head over this one. I'm importing data
from multiple workbooks, each workbook has a sheet called SubSAT and I
need to get specific data from that into the database.
I'm finding that if I have the workbook open on my desktop then the
SubSAT % complete and the High level plan % come in fine, but as soon
as the workbook is closed they come into the import table but the
target table will not accept the value. I've tried Cdbl and this
doesn't work, the difference appears to be then when it's open the
values come in as 0.69 but when it is closed they come in as 69% and
this upsets it.
I've put the offending code below, does anyone have any ideas as to
what is causing the difference in the data types, or what type
conversion will heal it?
All help much appreciated.
startcol = "A"
endcol = "M"
XLTARGET = "Temp_Data_SubSAT"
HLP_Start = 1 'high level plan start and end row are fixed and uniform
HLP_End = 16
AOD_Start = 17 'AOD list start row is fixed and uniform
AOD_End = 40 'AOD list end row is not fixed or uniform, this is as long
as I think it will get
Detail_Start = 26 'AOD detail start row, not fixed but this will be the
lowest
Detail_End = 300 'AOD detail end row, not fixed this is a guess at the
furthest it will go
DoCmd.SetWarnings False
DoCmd.RunSQL "delete * from Temp_Data_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_AOD_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from CSCI_Build_SubSAT" 'Clear table
DoCmd.RunSQL "delete * from Temp_Data_AOD_Detail_SubSAT" 'Clear table
Set xlApp = CreateObject("Excel.Application")
'Path of the file being imported
Workbook_Array = Array("D:\N15_Charts\FDP CTR iFACTS.xls")
'"D:\N15_Charts\FDP CTR iFACTS.xls", "D:\N15_Charts\ADS_&_TOOLS CTR
iFACTS.xls", "D:\N15_Charts\CMTOOLS CTR iFACTS.xls",
"D:\N15_Charts\MSRS_&_RAP CTR iFACTS.xls", "D:\N15_Charts\RDP & CMS CTR
iFACTS.xls", "D:\N15_Charts\SEC CTR iFACTS.xls", "D:\N15_Charts\WDM &
SCC CTR iFACTS.xls", "D:\N15_Charts\CGW_&_SIS CTR iFACTS.xls",
"D:\N15_Charts\CxSS 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
sheetRange = xlSheet.Name
If sheetRange Like "SubSAT" & "*" Then
range = startcol & HLP_Start & ":" & endcol & HLP_End
'Transfer the data from the worksheet
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel5, _
XLTARGET, _
strPath, _
False, _
sheetRange & "!" & range
Set rst_import =
Current_DB.OpenRecordset("Temp_Data_SubSAT")
Set rst_target_build =
Current_DB.OpenRecordset("CSCI_Build_SubSAT")
rst_import.MoveFirst
CSCI = rst_import!F1.Value
rst_import.MoveNext
rst_import.MoveNext
rst_target_build.AddNew
rst_target_build![CSCI] = CSCI
rst_target_build![Build] = Build
rst_target_build!SubSAT_ETC = rst_import!F7.Value
rst_import.MoveNext
rst_target_build![Planned End Date] =
CDate(rst_import!F7.Value)
rst_import.MoveNext
MsgBox "SubSAT % complete" & rst_import!F8.Value
rst_target_build![SubSAT_%_Comp] = rst_import!F8.Value
rst_import.MoveNext
MsgBox "HLP % complete" & CDbl(rst_import!F9.Value)
rst_target_build![High_Level_Plan_%_Comp] =
CDbl(rst_import!F9.Value)
rst_target_build.Update
End If
Next
'MsgBox "Complete " & strPath
Next
xlBook.Application.Quit
'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