Can Access Insert a Column into Excel

[LadyDi]

Is there a way for Access to Open an Excel spreadsheet, insert a column at the very beginning, put a formula in, and then copy the formula down? I know that sounds like a lot, but I know if anyone knows how to accomplish this, it will be on this forum. I'm trying to automate a process before I hand it off to someone else, and this is the only place where I get stuck. I've been able to automate everything except adding a column and inserting a formula (it's a fairly simple formula - just three nested IF statements). Any assistance you can provide would be greatly appreciated.

 

[Trevor G]

This one will open a workbook go to a sheet into a cell and then insert a column, my advice about the formula is to record a macro in Excel and open the workbook and then insert your column and also add your formula then that will give you the code you need.

Sub excel2()
Dim xlApp As Excel.Application
Set xlApp = CreateObject("Excel.Application")
With xlApp
.Visible = True
.Workbooks.Open "L:\Excel VBA\SampleNames.xlsx" 'Change to your workbook name
.Sheets(1).Select 'moves to the sheet
.Range("B2").Select
.Selection.EntireColumn.Insert 'This inserts a column

End With

End Sub

 

[boblarson]

Just an FYI

You don't need to select to copy.

 

[LadyDi]

Thank you very much for the information. I really appreciate it. I have been able to get Access to insert the new column and put the formula in cell A2. However, I am having difficulty getting it to copy the formula down the column. I tried using exactly what was in the Macro that I recorded - as you suggested doing with the formula. However, I keep getting an error message that states: "Autofill method of range class failed". Here is what I tried -
.Selection.AutoFill Destination:=Range("A2:A3285"), Type:=xlFillDefault
.Range("A2:A3285").Select
.Range("A1").Select

What am I doing wrong? I tried telling it to select the whole column as a range, instead of "selection" (incidently, in the line just before what I have copied here Excel selects cell A2). I tried using the words Range.Copy instead of Selection.AutoFill and that didn't work either. How can I get this formula to copy the whole way down the spreadsheet?

 

[boblarson]

Change to this:

[B][COLOR=red]Dim strRange As String[/COLOR][/B]
 
[B][COLOR=red]strRange = "A2:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row[/COLOR][/B]
.Range("A2").Select
.Selection.AutoFill Destination:=Range([B][COLOR=red]strRange[/COLOR][/B]), Type:=xlFillDefault
.Range("A1").Select

 

[LadyDi]

That's perfect. Thank you so much.

I just have one more question. I would also like to sort my spreadsheet by column C in ascending order (column C are the names of units) and then by column K in ascending order (column K are dates). Is there a way to do this too?

 

[boblarson]

That's perfect. Thank you so much.

I just have one more question. I would also like to sort my spreadsheet by column C in ascending order (column C are the names of units) and then by column K in ascending order (column K are dates). Is there a way to do this too?

My suggestion - this is how I get the code needed in Excel - is to take the spreadsheet, record a macro doing what you want it to do and then go to that code and tweak it to fit into what you are doing.

 

[LadyDi]

I was able to get this function to work. It now, opens the spreadsheet, sorts it by two criteria, inserts a column, enters a formula and copies it down. However, I have run into another problem today. The spreadsheet that I am loading into the database is sent to me from another department. That department is not being consistant with the set up of the spreadsheet. Sometimes, the tab I want is called "trackerincidentquery" and sometimes it is called "Report1(1)". It is also seldom in the same place. Sometimes it is the only tab in the workbook, other times, it is the third tab. I tried to adjust my function so that it would recognize one of two sheet names, but what I tried didn't work. I added a variable called SheetName. Then I declared it like this: SheetName = "trackerincidentquery" or "Report1(1)". When I try to run that, I get a "type mismatch" error. Is there a way to make the function look at the available tabs and select the tab that has one of these two names?

 

[Trevor G]

Worksheets can be identified with its real name so for instance

Sheets("Sheet1") its real name is Sheets(1) and Sheets("Somename") its real name maybe Sheets(2) If you compare the workbooks you have had with the different names on and see if in the VBA screen in the Project window if it shows you the real name being used in both workbooks are the same.

Are the other sheets in the workbook when it has more than one showing any data on them?

You could use a search method to identify a heading in the spreadsheet that is always consistent. An example is like this

Sub checksheet()
Dim ws As Worksheet
For Each ws In Worksheets
ws.Activate
If Range("d1") <> "" Then
MsgBox "Worksheet NAme is" & ws.Name
End If

Next ws

End Sub

 

[LadyDi]

Thank you for the information.

I have one more question. The last spreadsheet I received had formulas referring to something on the hard drive of the person who created it. As a result, I get the message that says "This workbook contains one or more links that cannot be updated. . . .". I have to click the Continue button, or press Enter to continue. Is it possible to build into the VBA to automatically press the enter key when the spreadsheet is opened? That way, if this message appears, the database will respond to it. But, if the message does not appear, all that will happen, is the cursor will move down one cell. Is this possible?

 

[boblarson]

You should be able to with your application object. If your application object is appXL, for example, you can use:

appXL.DisplayAlerts = False

' do your stuff here

appXL.DisplayAlerts = True

 

[LadyDi]

That worked great. One problem solved.

Unforunately, I've come across another problem. Here is the code that I have. I have checked to make sure that everything is tied to the Excel application. However, after the code finishes running, there is still an "EXCEL.exe" showing on the Processes tab of my Task Manager. Is there something I'm missing? How can I get that instance of Excel to go away?

Sub excel2()
Dim xlApp As Excel.Application
Dim strRange As String
Dim SortRange1 As String
Dim SortRange2 As String
Dim SheetName As String

Set xlApp = CreateObject("Excel.Application")
SheetName = "trackerincidentquery"
With xlApp
.Visible = True
.DisplayAlerts = False
.Workbooks.Open "R:\Fire Drills\Reboots\Star Download " & Month([Forms]![frmMain]!ReportDate) & Day([Forms]![frmMain]!ReportDate) & Right([Forms]![frmMain]!ReportDate, 2) & ".xlsx" 'Change to your workbook name
.Sheets(.ActiveSheet.Name).Select 'moves to the sheet
.Cells.Select
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Columns("A:W").Select
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort.SortFields.Clear
SortRange1 = "C2:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort.SortFields.Add Key:=Range(SortRange1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
SortRange2 = "K2:K" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort.SortFields.Add Key:=Range(SortRange2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort
.SetRange Range("A:W")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A2").Select
.Selection.EntireColumn.Insert 'This inserts a column
.ActiveCell.FormulaR1C1 = "=IF(RC[5]=313, IF(R[-1]C[5]=899, IF(RC[3] = R[-1]C[3], ""reboot"", """"), """"), """")"
strRange = "A2:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row
.Range("A2").Select
.Selection.AutoFill Destination:=Range(strRange), Type:=xlFillDefault
.Range("A1").Select
.DisplayAlerts = True
.ActiveWorkbook.Save
.ActiveWorkbook.Close
.Quit
End With
Set xlApp = Nothing
End Sub

 

[boblarson]

See the parts in red. You were close but not quite there;

.DisplayAlerts = False
.Workbooks.Open "R:\Fire Drills\Reboots\Star Download " & Month([Forms]![frmMain]!ReportDate) & Day([Forms]![frmMain]!ReportDate) & Right([Forms]![frmMain]!ReportDate, 2) & ".xlsx" 'Change to your workbook name
.Sheets(.ActiveSheet.Name).Select 'moves to the sheet
.Cells.Select
.Selection.Copy
.Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
.Columns("A:W").Select
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort .SortFields.Clear
SortRange1 = "C2:C" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort .SortFields.Add Key:=[B][COLOR=red]Range[/COLOR][/B](SortRange1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
SortRange2 = "K2:K" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row
.ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort .SortFields.Add Key:=[B][COLOR=red]Range[/COLOR][/B](SortRange2), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With .ActiveWorkbook.Worksheets(.ActiveSheet.Name).Sort
.SetRange Range("A:W")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
.Range("A2").Select
.Selection.EntireColumn.Insert 'This inserts a column
.ActiveCell.FormulaR1C1 = "=IF(RC[5]=313, IF(R[-1]C[5]=899, IF(RC[3] = R[-1]C[3], ""reboot"", """"), """"), """")"
strRange = "A2:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell) .Row
.Range("A2").Select
.Selection.AutoFill Destination:=[B][COLOR=red]Range[/COLOR][/B](strRange), Type:=xlFillDefault

 

[LadyDi]

I added a period in front of the word Range in each instance highlighted. However, I'm still seeing the EXCEL.exe on the Processes tab of my Task Manager after the code has run. What should I do next?