Good morning. I have data coming from a different server that I need to import into my access db. The data is in csv form and when converted to xlsx has blank columns and rows that need to be deleted. I am attempting to do a multistep process to:
1. convert csv file to xlsx. Is this possible? Have been unable to locate information as to how to do this.
2. Delete blank columns for proper import. Will tackle when I get step 3 completed.
3. Delete first 21 rows of the worksheet (there is built in code to create other information and picture preventing proper import into access) I either need to delete the rows or start on row 22. Still trying to figure out if I can select the row to start the import. I have starting dabbling in the code, but am having a hard time with access recognizing the worksheet that is open. Do not know why.
4. Save the new sheet.
5. Import the data into a preexisting table. This part I have done and is working great on the other data I import.
Question, is the above possible? If so, any insight into as to resources to accomplish this?
Below is the code I have been playing with. It opens the workbook, but then I get the error "Please check error 1004 Sorry, we couldn't find LIRxlsx.xlsx. Is it possible it was moved, renamed or deleted?" The spreadsheet is open. Why can it not recognize the workbook?
Code:
Option Compare Database
Option Explicit
Dim wb As Excel.Workbook
Dim xlApp As Excel.Application
Dim FilePicker As FileDialog
Dim SelectedFile As String
Dim vrtSelectedItem As Variant
Private Sub cmdDeleteRows_Click()Set FilePicker = Application.FileDialog(msoFileDialogFilePicker)
Set xlApp = CreateObject("Excel.Application")
On Error GoTo Err_Display
FilePicker.AllowMultiSelect = True
FilePicker.Filters.Add "Excel", "*.xls*", 1
FilePicker.InitialFileName = "C:\Users\"
FilePicker.Title = "Please Select the Excel Data..."
FilePicker.Show
For Each vrtSelectedItem In FilePicker.SelectedItems
Application.FollowHyperlink vrtSelectedItem
Next
'xlApp.Visible = True (if using filepicker, then this is opening another excel worksheet. Not used here as we are opening a specific file
'Set wb = xlApp.Workbooks.Open("LIRxlsx.xlsx", True, False):Failed as it does not recognize the workbook
Set wb = xlApp.Workbooks.Open("LIRxlsx", True, False)
wb.Sheets(1).Rows(2).Delete
'if more than one sheet in wb, then use code below
'wb.Sheets("Sheet2").Rows(2).Delete
Err_Exit:
Exit Sub
Err_Display:
MsgBox "Please check error " & Err.Number & " " & Err.Description
Resume Err_Exit
End Sub
Any insight to my thought process would be very helpful. I even thought maybe I don't have the right references turned on under tools as I have been playing with this idea for several hours and every time I try an approach, I get errors. Hummmm I could even be looking at this process all wrong.
Thank you!