madeleine,
I think it is not the most easy and flexible way to import data from
Excel-worksheets but I think the main obstacle could be the numeric
datatype used in the target table. To be able to accept percentual
values this should be some form of floating point format or fractional
data accepting datatype and not some form of scaled integer type often
used when declared as numeric datatype. a cdbl-function doesn't change
the receiving field-datatype, but only will convert the source data
into double precision floating point format before deliverig it to your
target table. If this contains some form of integer datatype
(implicitly scaled integers as well) it wil throw away the fractional
data expected from a percentage-value below 100%. If a scaled type is
used with a scalefactor of 1000 (3 digits behind the decimal point) the
values first have to be muliplicated with 1000 to be accepted.
If I would import some values from Excel-sheets I would consider giving
the cells or ranges a unique and meaningful name within the spreadsheet
and importing them as tables from an Excel-sheet directly. In that case
however a setup of the Excel sheet that cannot easily be corrupted by
end-users containing other than the ecpectyed values and datatypes is
needed to prevent worthless input. It all can be done and even
automated upto some point without use of VBA. Only if full automation
and extensive detection of possible errors, full validation of the
spreadsheet and logging of all imports is needed use of VBA will not be
avoidable.
Marc
madeleine schreef:
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
