hi people, i have following code that i atatched to command36 button to
import excel file (HKG.xls) at "input" tab from a folder C:\Country. this will be put in a new table called "importCtryTest7". a problem i have here is the import works but not fully. I had missing data and upon research, i encountered the type conversion error - import errors. these i believed are caused by calculated fields in the file and is resolved by using paste special as values to format these fields (after whcih i do not have import errors). The issue is i am planning to loop as many as 100 of these files and the calculated fields need to stay in the file as the files are also used for other purpose whcih uses the calculations in the fields.
is there a way to automate paste special as values in vba (so i dont have to manually open up 100 files every month to copy/paste special) so i dont get the import errors? i surfed the net for this as found some hints (http://www.ozgrid.com/forum/showthread.php?t=21846&page=1) but couldnt get the vba to work after inserting the paste special code given in the link.
i am bad in vba so appreciate if anyone can advise me on how to modify below code to automate paste special values (whcih i deduce is possible based on the discussion in the link)
Thanks!
Private Sub Command36_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim i As Integer
strTable = "ImportCtryTest7"
Dim strWorkbooks(1) As String
Dim strWorksheets(1) As String
strWorkbooks(1) = "HKG.xls"
strWorksheets(1) = "Input"
blnHasFieldNames = True
'strPath = "C:\Country"
' Create loop to import from the 3 different Workbooks
For i = 1 To 1
strFile = Dir(strPath & strWorkbooks(i))
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames, _
strWorksheets(i) & "$"
strFile = Dir()
Loop
Next i
End Sub
import excel file (HKG.xls) at "input" tab from a folder C:\Country. this will be put in a new table called "importCtryTest7". a problem i have here is the import works but not fully. I had missing data and upon research, i encountered the type conversion error - import errors. these i believed are caused by calculated fields in the file and is resolved by using paste special as values to format these fields (after whcih i do not have import errors). The issue is i am planning to loop as many as 100 of these files and the calculated fields need to stay in the file as the files are also used for other purpose whcih uses the calculations in the fields.
is there a way to automate paste special as values in vba (so i dont have to manually open up 100 files every month to copy/paste special) so i dont get the import errors? i surfed the net for this as found some hints (http://www.ozgrid.com/forum/showthread.php?t=21846&page=1) but couldnt get the vba to work after inserting the paste special code given in the link.
i am bad in vba so appreciate if anyone can advise me on how to modify below code to automate paste special values (whcih i deduce is possible based on the discussion in the link)
Thanks!
Private Sub Command36_Click()
Dim strPathFile As String, strFile As String, strPath As String
Dim strTable As String
Dim blnHasFieldNames As Boolean
Dim i As Integer
strTable = "ImportCtryTest7"
Dim strWorkbooks(1) As String
Dim strWorksheets(1) As String
strWorkbooks(1) = "HKG.xls"
strWorksheets(1) = "Input"
blnHasFieldNames = True
'strPath = "C:\Country"
' Create loop to import from the 3 different Workbooks
For i = 1 To 1
strFile = Dir(strPath & strWorkbooks(i))
Do While Len(strFile) > 0
strPathFile = strPath & strFile
DoCmd.TransferSpreadsheet acImport, _
acSpreadsheetTypeExcel9, strTable, _
strPathFile, blnHasFieldNames, _
strWorksheets(i) & "$"
strFile = Dir()
Loop
Next i
End Sub
