How to Update excel Cells via VBA

[lmarconi]

Good afternoon. I have the following issue: in my application i have an importing function which look in a dedicated folder and import the some specific columns of the present Excel files (one or many) in my tables.
The issue is that, sometime, the users who sent the excel file, forgot to remove some filters (always located in the Cell B1 and B2) and this means that some data are not imported. What i would like to do is, before to import the file, the system should open it, look at the column B1 and B2 and replace with "(All)" any other value is present and save the file. In this way i'm sure I remove any filter and all data are well imported.

This is the code i wrote looking also other messages but i have 2 questions:
1. How to look at 2 cells (this code look only to B1, i discover after that i need also B2)
2. How to change the value of those 2 cells if is <> from "(ALL)"

Dim xl As Object, rFound As Object


Set xl = CreateObject("Excel.Application")

On Error GoTo errorlog

With xl.Workbooks.Open(myfile, , True)
With .Sheets("Statistics")
'look in sheet Statistics column B1 (!!!!! HOW TO LOOK AT 2 CELL ??)
Set rFound = .Columns(2).Find(What:=.Range("B1").value, LookIn:=xlValues, LookAt:=xlWhole)

If Not rFound = "(All)" Then
'--------HOW TO CHANGE TO ALL ?????-----

End If
End With

Thank you for usual and kindly support.
L.

[Welshgasman]

I would just remove filters all together?
Then if the filters include B3 it will not matter.

[lmarconi]

I would just remove filters all together?
Then if the filters include B3 it will not matter.

Thanks you for the answer. If there are not any implication and this solve my problem, fully agree but can you suggest me how to do ?
Thank you.

[ranman256]

If range(“A1”).value then beep.
If range(“A2”).value then beep.

[CJ_London]

in excel create a macro by recording your actions.

then look at the resulting code and adapt to your requirements

[lmarconi]

If range(“A1”).value then beep.
If range(“A2”).value then beep.

Thanks for the answer but, sorry, it don't help me, maybe for you is clear, not for me.

With this i look the value of the first filter at column B1:
Set rFound = .Columns(2).Find(What:=.Range("B1").value, LookIn:=xlValues, LookAt:=xlWhole)

I can think to do the same with a second variable for B2 if nobody suggest something more smart, but the main problem is how to remove any filter in that Cell and use 'All'.
I add another thing: the filters is part of a Pivot Table.
Thanks

[Welshgasman]

Thanks you for the answer. If there are not any implication and this solve my problem, fully agree but can you suggest me how to do ?
Thank you.

The easiest way is as Ajax described, then adapt.? That is one good thing about Excel, you can record your actions and amend to suit.
However I also have access to a search engine, so look at the links from the link below.

https://www.startpage.com/do/search?...rtpage.vivaldi

[lmarconi]

Thank you to all suggestion and yes, using the Macro record functionality it helps to understand how to do. Following is my function there is still a problem i can't understand: the file is not saved, regardless ask me if i want to replace.
Just for your information the Excel file is a ".xlsb" type. Any idea ?

This is the function I use:

Dim xl As Object
Set xl = CreateObject("Excel.Application")

With xl.Workbooks.Open(myfile, , True)
With .Sheets("Statistics")
Range("B1").Select
.PivotTables("PivotTable1").PivotFields("ASSET").O rientation = xlHidden
Range("B2").Select
.PivotTables("PivotTable1").PivotFields("CLUSTER") .Orientation = xlHidden
End With
'
'xl.DisplayAlerts = False - I just remark to see if asked me to save.
.Save
.Close
'xl.DisplayAlerts = True
End With
Set xl = Nothing

[Welshgasman]

Try
With xl.Sheets
Also please post your code indented within code tags.

[lmarconi]

Try
With xl.Sheets
Also please post your code indented within code tags.

Good morning. I modify the code as suggested:

'------------------------------------------------------------------
Function xls_update(myfile)
Dim xl As Object
Set xl = CreateObject("Excel.Application")
With xl.Workbooks.Open(myfile, , True)
With xl.Sheets("Statistics")
Range("B1").Select
.PivotTables("PivotTable1").PivotFields("ASSET").O rientation = xlHidden
Range("B2").Select
.PivotTables("PivotTable1").PivotFields("CLUSTER") .Orientation = xlHidden
End With
.Save
.Close
End With
Set xl = Nothing
End Function
'---------------------------------------------------

and i receive this:




and regardless i answer "YES", the file is not saved and I don't understand why.
Cheers,

[Welshgasman]

Ok, sorry, that does not make sense?
Zip required objects and upload.

[lmarconi]

Ok, sorry, that does not make sense?
Zip required objects and upload.

Sorry, I don't understand. Where i used Zip ?
The problem was that the file which i open, it was read only, hence not allow to save unless i change name, so this is what i did:

'------------------------------------------------------------
Function xls_update(myfile, myfilenew)
** myfile is the origin file - myfilenew is the new i will saveas **


Dim xl As Object
Set xl = CreateObject("Excel.Application")
With xl.Workbooks.Open(myfile, , True)
With xl.Sheets("Statistics")
xl.Sheets("Statistics").Range("B1").Select
.PivotTables("PivotTable1").PivotFields("ASSET").O rientation = xlHidden
xl.Sheets("Statistics").Range("B2").Select
.PivotTables("PivotTable1").PivotFields("CLUSTER") .Orientation = xlHidden
End With
xl.DisplayAlerts = False
myfilenew = Mid(myfile, 1, Len(myfile) - 5) & "-R1.xlsb"
.SaveAs fileName:=myfilenew
.Close
xl.DisplayAlerts = True
End With
Kill (myfile)
Set xl = Nothing
End Function
'----------------------------------------------------------

Now looks like there is another issue. As i said, my procedure open one by one one or multiple files in the selected folder. If the file is only one, no problem, this function work fine, if the files are more than one, i got the following error



And this happen at the line:

** xl.Sheets("Statistics").Range("B1").Select **

I read somewhere that it seams the Range is not correct, but this is not possible or i did something wrong. I purposely add "xl.Sheets("Statistics")" before Range, in case this was the problem (but i don't think).

Thanks.
L.

[Welshgasman]

I think you need to fully qualify all the objects.?
It appears this always works once, but then falls over on any subsequent use.

https://www.startpage.com/do/search?...rtpage.vivaldi

[Micron]

Please post your code within code tags as requested (use # button on posting toolbar) and properly indent to make it easier to troubleshoot. I don't have your sheet or pivot table so I've commented those out to illustrate things like how to post code, indent, use With blocks correctly, etc. You cannot save a regular file as a binary file (xlsb) just by changing the extension in the name. Correct method shown.

Function xls_update(myfile, myfilenew)
'** myfile is the origin file - myfilenew is the new i will saveas **
Dim xl As Object

Set xl = CreateObject("Excel.Application")
With xl.Workbooks.Open(myfile, , True)
   ''With xl.Sheets("Statistics")
   With xl.Sheets("Sheet1")
      ''xl.Sheets("Statistics").Range("B1").Select
      .Range("B1").Select
      ''.PivotTables("PivotTable1").PivotFields("ASSET").Orientation = xlHidden
      ''xl.Sheets("Statistics").Range("B2").Select
      .Range("B2").Select
      ''.PivotTables("PivotTable1").PivotFields("CLUSTER").Orientation = xlHidden
   End With
   xl.DisplayAlerts = False
   myfilenew = Mid(myfile, 1, Len(myfile) - 5) & "-R1.xlsb"
   .SaveAs Filename:=myfilenew, FileFormat:=50
   .Close
   xl.DisplayAlerts = True
End With

'Kill (myfile)
Set xl = Nothing

End Function

If you still get that error, check your sheet name spelling - note that sheet names are case sensitive.

[lmarconi]

Please post your code within code tags as requested (use # button on posting toolbar) and properly indent to make it easier to troubleshoot. I don't have your sheet or pivot table so I've commented those out to illustrate things like how to post code, indent, use With blocks correctly, etc. You cannot save a regular file as a binary file (xlsb) just by changing the extension in the name. Correct method shown.

Function xls_update(myfile, myfilenew)
'** myfile is the origin file - myfilenew is the new i will saveas **
Dim xl As Object

Set xl = CreateObject("Excel.Application")
With xl.Workbooks.Open(myfile, , True)
   ''With xl.Sheets("Statistics")
   With xl.Sheets("Sheet1")
      ''xl.Sheets("Statistics").Range("B1").Select
      .Range("B1").Select
      ''.PivotTables("PivotTable1").PivotFields("ASSET").Orientation = xlHidden
      ''xl.Sheets("Statistics").Range("B2").Select
      .Range("B2").Select
      ''.PivotTables("PivotTable1").PivotFields("CLUSTER").Orientation = xlHidden
   End With
   xl.DisplayAlerts = False
   myfilenew = Mid(myfile, 1, Len(myfile) - 5) & "-R1.xlsb"
   .SaveAs Filename:=myfilenew, FileFormat:=50
   .Close
   xl.DisplayAlerts = True
End With

'Kill (myfile)
Set xl = Nothing

End Function

If you still get that error, check your sheet name spelling - note that sheet names are case sensitive.

Thank you first of all and apologies if i didn't use the correct way. Honestly my code has indent but when i copy & paste i didn't noticed that, for some reason, everything was aligned. I will be more care next time.
Before you answer i solved the issue in the following way, i'm not sure is the best solution, but works fine with single and multiple file and without error :




Refer to your comment "You cannot save a regular file as a binary file (xlsb)": as i mentioned above, all my files are already (xlsb) format so, what i did is only to change name adding -R1 (myfilenew = Mid(myfile, 1, Len(myfile) - 5) & "-R1.xlsb").
I will test your solution because looks logically much better and thanks again to all for usual and kindly support.
Cheers.
L.