Deleting columns in Excel Sheet from Access VB Code

[New_Kid_in_Block]

Hello Everyone

I am quite new to the world of VB coding in Access. I have written down a code to Import Excel sheet into Access07.

The table has been made accordingy and it works fine. it imports the worksheet "closing12" to tables 'Fixed_Asset_Listing' and 'NBV_Fixed_Asset_Listing'.

I am having problems writing code for deleting column name "sortfield" from the excel sheet. Here is a twist. the reason I want to do so is because the excel sheet generated from the software can have as many sortfields as user want and will be names as sortfield1, sortfield2, sortfield3 and so on.... it is garbage data and I want to delete all the columns with name sortfield1, sortfield2, sortfield3 and so on.... before i do the import process.

While Mid(xlSheet.Range("A2").Value, 1, 9) = "Sortfield"
xlSheet.Range("A2").EntireColumn.Delete
Wend

Please help me with correcting the added code. Ur help will be really appreicatated.

Whole code is written down.

Thanks and Regards,
Prashant Kumar.





Private Sub Import_Fixed_Asset_Listing_Click()
Dim filename As String
Dim OpenFile As OPENFILENAME
Dim lReturn As Long
Dim sFilter As String
OpenFile.lStructSize = Len(OpenFile)
sFilter = "Excel Files (*.xls)" & Chr(0) & "*.XLS" & Chr(0)
OpenFile.lpstrFilter = sFilter
OpenFile.nFilterIndex = 1
OpenFile.lpstrFile = String(257, 0)
OpenFile.nMaxFile = Len(OpenFile.lpstrFile) - 1
OpenFile.lpstrFileTitle = OpenFile.lpstrFile
OpenFile.nMaxFileTitle = OpenFile.nMaxFile
OpenFile.lpstrInitialDir = "C:\"
OpenFile.lpstrTitle = "Import Fixed Asset Listing"
OpenFile.flags = 0
lReturn = GetOpenFileName(OpenFile)
If lReturn = 0 Then
MsgBox "No File Selected. Abort!"
Else
filename = Trim(OpenFile.lpstrFile)
DoCmd.Close acTable, "Fixed_Asset_Listing", acSaveYes
Set xls = CreateObject("Excel.Application")
Set xlWB = xls.workbooks.Open(filename)
xls.Visible = True
Set xlSheet = xlWB.Worksheets("Closing12")
While Mid(xlSheet.Range("A2").Value, 1, 9) = "Sortfield"
xlSheet.Range("A2").EntireColumn.Delete
Wend

xlWB.Save

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"Fixed_Asset_Listing", filename, True, "Closing12!"


DoCmd.OpenQuery "Delete_Querry(data_of_table_NVB_FAL)"
DoCmd.Close acTable, "NBV_Fixed_Asset_Listing", acSaveYes
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel8, _
"NBV_Fixed_Asset_Listing", filename, True, "Closing12!"

xls.Quit
Set xlWB = Nothing
Set xls = Nothing

DoCmd.Save
End If
'Transfers file using acImportDelim profile previously set through manually importing into table
'DoCmd.TransferSpreadsheet(acImport,acSpreadsheetTypeExcel8,"temp",filename,0,"A2:v67",1) =
End Sub

 

[DCrake]

I haven't got 07 installed but if my memory serves me correctly when you use TransferSpreadsheet and use an import specification you can nominate which fields to import and which to skip. Surely you only need to skip the column in question.


David