By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,949 Members | 946 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,949 IT Pros & Developers. It's quick & easy.

Alter a Created Spreadsheet After Export

100+
P: 121
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.
May 6 '07 #1
Share this Question
Share on Google+
6 Replies


100+
P: 121
This is my current code:

Expand|Select|Wrap|Line Numbers
  1. Private Function Connect(ByVal strVvPathAndFileNameOfExcelSpreadsheet As String) As Boolean
  2. ' Make and open a connection to a given Excel spreadsheet
  3.  
  4.   Set cnMvConnection = New ADODB.Connection
  5.   With cnMvConnection
  6.     .Provider = "Microsoft.Jet.OLEDB.4.0"
  7.     .ConnectionString = "Data Source=" & strVvPathAndFileNameOfExcelSpreadsheet & ";Extended Properties=Excel 8.0;"
  8.     .Open
  9.     .Sheets("ERR_REPRT").Select
  10.     .Rows("2:2").Select
  11.     .Selection.Entirerow.Hidden = True
  12.   End With
  13.  
  14.  
  15. End Function
  16.  
  17. Function CHANGE_XLS()
  18.  
  19.  
  20. Call Connect("C:\TEST.xls")
  21. End Function
But I get an "Arguments are of the wrong type, are out of acceptable range or in direct conflict with another...." error
May 6 '07 #2

ADezii
Expert 5K+
P: 8,619
This is my current code:

Expand|Select|Wrap|Line Numbers
  1. Private Function Connect(ByVal strVvPathAndFileNameOfExcelSpreadsheet As String) As Boolean
  2. ' Make and open a connection to a given Excel spreadsheet
  3.  
  4.   Set cnMvConnection = New ADODB.Connection
  5.   With cnMvConnection
  6.     .Provider = "Microsoft.Jet.OLEDB.4.0"
  7.     .ConnectionString = "Data Source=" & strVvPathAndFileNameOfExcelSpreadsheet & ";Extended Properties=Excel 8.0;"
  8.     .Open
  9.     .Sheets("ERR_REPRT").Select
  10.     .Rows("2:2").Select
  11.     .Selection.Entirerow.Hidden = True
  12.   End With
  13.  
  14.  
  15. End Function
  16.  
  17. Function CHANGE_XLS()
  18.  
  19.  
  20. Call Connect("C:\TEST.xls")
  21. 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
Expand|Select|Wrap|Line Numbers
  1. .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.
May 7 '07 #3

JConsulting
Expert 100+
P: 603
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
May 7 '07 #4

100+
P: 121
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
May 7 '07 #5

100+
P: 121
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:

Expand|Select|Wrap|Line Numbers
  1. Function HideRow()
  2.     Dim xlApp As Object
  3.     Dim xlWB As Object
  4.     'Dim xlWS As Worksheet
  5.     Dim strSheet As String
  6.     Set xlApp = CreateObject("Excel.Application")
  7.     Set xlWB = xlApp.Workbooks.Open("C:\Test.xls", True)
  8.     Set xlWS = xlWB.Sheets(1)
  9.     xlApp.Visible = True
  10.  
  11.         With xlWS
  12.             xlWS.Rows("1:1").Select
  13.             Selection.ColorIndex = 55
  14.                     .Pattern = xlSolid
  15.                     .PatternColorIndex = xlAutomatic
  16.  
  17.             'xlWS.Selection.EntireRow.Hidden = True
  18.         End With
  19.  
  20.     xlWB.Close (True) '<saves it
  21.     xlApp.Quit
  22. End Function
Now I get an object required and it stops at "Selection.Colorindex = 55".

Any ideas?

Thanks for everyone's help....
May 14 '07 #6

JConsulting
Expert 100+
P: 603
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:

Expand|Select|Wrap|Line Numbers
  1. Function HideRow()
  2.     Dim xlApp As Object
  3.     Dim xlWB As Object
  4.     'Dim xlWS As Worksheet
  5.     Dim strSheet As String
  6.     Set xlApp = CreateObject("Excel.Application")
  7.     Set xlWB = xlApp.Workbooks.Open("C:\Test.xls", True)
  8.     Set xlWS = xlWB.Sheets(1)
  9.     xlApp.Visible = True
  10.  
  11.         With xlWS
  12.             xlWS.Rows("1:1").Select
  13.             Selection.ColorIndex = 55
  14.                     .Pattern = xlSolid
  15.                     .PatternColorIndex = xlAutomatic
  16.  
  17.             'xlWS.Selection.EntireRow.Hidden = True
  18.         End With
  19.  
  20.     xlWB.Close (True) '<saves it
  21.     xlApp.Quit
  22. 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.

Expand|Select|Wrap|Line Numbers
  1. Private Declare Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As Long)
  2.  
Now use this as a model to place the "Sleep" into your code...it will give Excel time to catch up.

Expand|Select|Wrap|Line Numbers
  1. Function HideRow(sFile As String) As String
  2.     Dim xlApp As Object
  3.     Dim xlWb As Object
  4.     Dim xlWS As Worksheet
  5.     Dim strSheet As String
  6.     Set xlApp = CreateObject("Excel.Application")
  7.     Set xlWb = xlApp.Workbooks.Open(sFile, True)
  8.     Set xlWS = xlWb.Sheets(1)
  9.     xlApp.Visible = True
  10.     xlWS.Rows("1:1").Select
  11.     Sleep (2000)
  12.     Selection.EntireRow.Hidden = True
  13.     xlWb.Close (True) '<saves it
  14.     xlApp.Quit
  15. End Function
  16.  
J
May 14 '07 #7

Post your reply

Sign in to post your reply or Sign up for a free account.