Alter a Created Spreadsheet After Export

[JHNielson]

I have a process that exports a set of records from Access to a Spreadsheet. I would like to hide the second row if possible.

Is there a way to do this through VB?


Thanks for any help I can get.

[JHNielson]

This is my current code:

Private Function Connect(ByVal strVvPathAndFileNameOfExcelSpreadsheet As String) As Boolean
' Make and open a connection to a given Excel spreadsheet
 
  Set cnMvConnection = New ADODB.Connection
  With cnMvConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strVvPathAndFileNameOfExcelSpreadsheet & ";Extended Properties=Excel 8.0;"
    .Open
    .Sheets("ERR_REPRT").Select
    .Rows("2:2").Select
    .Selection.Entirerow.Hidden = True
  End With
 
 
End Function
 
Function CHANGE_XLS()
 
 
Call Connect("C:\TEST.xls")
End Function

But I get an "Arguments are of the wrong type, are out of acceptable range or in direct conflict with another...." error

[ADezii]

This is my current code:

Private Function Connect(ByVal strVvPathAndFileNameOfExcelSpreadsheet As String) As Boolean
' Make and open a connection to a given Excel spreadsheet
 
  Set cnMvConnection = New ADODB.Connection
  With cnMvConnection
    .Provider = "Microsoft.Jet.OLEDB.4.0"
    .ConnectionString = "Data Source=" & strVvPathAndFileNameOfExcelSpreadsheet & ";Extended Properties=Excel 8.0;"
    .Open
    .Sheets("ERR_REPRT").Select
    .Rows("2:2").Select
    .Selection.Entirerow.Hidden = True
  End With
 
 
End Function
 
Function CHANGE_XLS()
 
 
Call Connect("C:\TEST.xls")
End Function

But I get an "Arguments are of the wrong type, are out of acceptable range or in direct conflict with another...." error

I do believe that your syntax is all wrong. The Error occurs on

.Sheets("ERR_REPRT").Select

and will also occur on subsequent lines given a chance since you are attempting to reference Excel Objects (Rows, Sheets, Selection) from an ADODB Connection Object. I can't see how this could ever be successful. Your best bet may be to Export the Spreadsheet via VBA's TransferSpreadsheet Method, create an Automation Session with Excel, hide the appropriate Range, then Save your work and end the Automation Session with Excel.

[JConsulting]

This seems to work well enough. It will bomb if you run it twice, because row 1 is already hidden at that point.

This requires that you have your Excel reference checked in your VBA references in Access.

you can modify to suit..this code is applied to sheet 1, but can be modified to use any sheet name.

Function HideRow(sFile As String) As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWS As Worksheet
Dim strSheet As String
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(sFile, True)
Set xlWS = xlWb.Sheets(1)
xlApp.Visible = True
xlWS.Rows("1:1").Select
Selection.EntireRow.Hidden = True
xlWb.Close (True) '<saves it
xlApp.Quit
End Function

[JHNielson]

This seems to work well enough. It will bomb if you run it twice, because row 1 is already hidden at that point.

This requires that you have your Excel reference checked in your VBA references in Access.

you can modify to suit..this code is applied to sheet 1, but can be modified to use any sheet name.

Function HideRow(sFile As String) As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWS As Worksheet
Dim strSheet As String
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(sFile, True)
Set xlWS = xlWb.Sheets(1)
xlApp.Visible = True
xlWS.Rows("1:1").Select
Selection.EntireRow.Hidden = True
xlWb.Close (True) '<saves it
xlApp.Quit
End Function

Great! Looks good. I will try as soon as I get a chance.

Thanks

[JHNielson]

This seems to work well enough. It will bomb if you run it twice, because row 1 is already hidden at that point.

This requires that you have your Excel reference checked in your VBA references in Access.

you can modify to suit..this code is applied to sheet 1, but can be modified to use any sheet name.

Function HideRow(sFile As String) As String
Dim xlApp As Object
Dim xlWb As Object
Dim xlWS As Worksheet
Dim strSheet As String
Set xlApp = CreateObject("Excel.Application")
Set xlWb = xlApp.Workbooks.Open(sFile, True)
Set xlWS = xlWb.Sheets(1)
xlApp.Visible = True
xlWS.Rows("1:1").Select
Selection.EntireRow.Hidden = True
xlWb.Close (True) '<saves it
xlApp.Quit
End Function

Sorry it took me so long to get back to you, but I tried it, and it gets me close, but when it goe to execute the " Selection.EntireRow.Hidden = True"
I get an error: "Object doesn't support this property or method."

Any ideas?

Also, I was trying to add another step here, so Itred this code ot change the row to blue:

Function HideRow()
    Dim xlApp As Object
    Dim xlWB As Object
    'Dim xlWS As Worksheet
    Dim strSheet As String
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Test.xls", True)
    Set xlWS = xlWB.Sheets(1)
    xlApp.Visible = True
 
        With xlWS
            xlWS.Rows("1:1").Select
            Selection.ColorIndex = 55
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
 
            'xlWS.Selection.EntireRow.Hidden = True
        End With
 
    xlWB.Close (True) '<saves it
    xlApp.Quit
End Function

Now I get an object required and it stops at "Selection.Colorindex = 55".

Any ideas?

Thanks for everyone's help....

[JConsulting]

Sorry it took me so long to get back to you, but I tried it, and it gets me close, but when it goe to execute the " Selection.EntireRow.Hidden = True"
I get an error: "Object doesn't support this property or method."

Any ideas?

Also, I was trying to add another step here, so Itred this code ot change the row to blue:

Function HideRow()
    Dim xlApp As Object
    Dim xlWB As Object
    'Dim xlWS As Worksheet
    Dim strSheet As String
    Set xlApp = CreateObject("Excel.Application")
    Set xlWB = xlApp.Workbooks.Open("C:\Test.xls", True)
    Set xlWS = xlWB.Sheets(1)
    xlApp.Visible = True
 
        With xlWS
            xlWS.Rows("1:1").Select
            Selection.ColorIndex = 55
                    .Pattern = xlSolid
                    .PatternColorIndex = xlAutomatic
 
            'xlWS.Selection.EntireRow.Hidden = True
        End With
 
    xlWB.Close (True) '<saves it
    xlApp.Quit
End Function

Now I get an object required and it stops at "Selection.Colorindex = 55".

Any ideas?

Thanks for everyone's help....

Often when using excel automation...the code runs faster than Excel does.

Place this at the top of a Code Module.

Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
 

Now use this as a model to place the "Sleep" into your code...it will give Excel time to catch up.

Function HideRow(sFile As String) As String
    Dim xlApp As Object
    Dim xlWb As Object
    Dim xlWS As Worksheet
    Dim strSheet As String
    Set xlApp = CreateObject("Excel.Application")
    Set xlWb = xlApp.Workbooks.Open(sFile, True)
    Set xlWS = xlWb.Sheets(1)
    xlApp.Visible = True
    xlWS.Rows("1:1").Select
    Sleep (2000)
    Selection.EntireRow.Hidden = True
    xlWb.Close (True) '<saves it
    xlApp.Quit
End Function
 

J