Saving excel sheet with few column names present as file name

[Shilpa Jain]

Hi All,

I am creating and saving the excel sheet at runtime(it is a copy of report generated in flexgrid). I need to add some of the column names in the excel sheet name while saving. e.g. Excel sheet should be saved as Report_Nid_Status_RBAC_Status_Today'sDate.

Nid_Status and RBAC_Status are column names in the sheet. Columns can vary and so the sheet name according to the columns. How I can save an excel sheet like this?

Regards,
Shilpa

[Shilpa Jain]

Please help me in sorting out the above mentioned problem. It is giving me nightmares. I have saved the file like Report_Today'sDate but adding variable column names is creating problems for me.

Regards,
Shilpa

[P90K]

To save the excel workbook with vb you need to use next line:

xl.activeworkbook.saveas "C:\... " & var1 & "text" & var2 & var3 ... ".xls"

I hope this was helpfull for you.

gr

[Shilpa Jain]

Thanks for replying. But , I am unable to get what you are trying to say. As per my understanding, this can be used only when the names are fixed. Correct me if I am wrong.

I have 3 columns in my report viz. NID_Status,ISD_Status,RBAC_status. Report can containg any of these 3 fields or a combination of them. For example, It can be Report_NID_Status_ISD_Status_TodaysDate if it contains only NID and Rbac columns or it can be Report_NID_Status_TodaysDate if only NID column is present or Report_NID_Status_ISD_Status_RBCA_Status_TodaysDate if all 3 columns are present in the report. The report name will vary depending which all columns are present in it. How do I incorporate these variable names in the report.

As of now, I am saving the file in Report_Date format as:

ApExcel.ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\Report_" & _
Format(Now(), "dd-mm-yyyy_hhmm") & ".xls", _
CreateBackup:=False

Please help.

Regards,
Shilpa

[P90K]

I don't think I completly understand you question.
you want to know how you read the name from the cell (in an excel file) and then dicide if the name need to be in the title.

Is that you question ??
or am I wrong??

gr

PS I'm sorry for my englisch.

[Shilpa Jain]

You have almost understand my question. I have atleast 5 columns in my report at a time. One of them would be like NID_Status. I only need to insert the *_Status column in my report name. Others should not be there.

I am attaching few samples of the report. It is named in the format of Report_Date_Time. It should be Report_*_Status_*_Status_Date_Time.The columns containing _Status only have to be present in the report name.

I hope you have understood my problem and will help me.

Regards,
Shilpa

[P90K]

K. I think I understand your problem.
I can not read you attachment for some reason. did you make that in excel???
because when I opend it with excel I get an error that the file was not created with excel.

I will try to explain

I asume your collum titels are in row 1 collum 1 to 5 ???

Dim var1, var2, var3, var4, var5
Dim ApExcel As Excel.Application 
Set ApExcel = New Excel.Application

then you open your workbook or make a new and fil it in.
then, when you want to save the file, you use next code.

Windows("name of your excel sheet.xls").Activate    'some times nessesary
if cells(1,1) <> "" then
          var1 = cells(1,1) & "_Status_"
end if
if cells(1,2) <> "" then
           var2=cells(1,2) & "_Status_"
end if
if cells(1,3) <> "" then
           var3=cells(1,3) & "_Status_"
end if
if cells(1,4) <> "" then
           var4=cells(1,4) & "_Status_"
end if
if cells(1,5) <> "" then
           var5=cells(1,5) & "_Status_"
end if

ApExcel.ActiveWorkbook.SaveAs FileName:="C:\Documents and Settings\All Users\Desktop\Report_" & var1 & var2 & var3 & var4 & var5 &
Format(Now(), "dd-mm-yyyy_hhmm") & ".xls"

I hope this solve you problem or was a little bit use full to solve you problem.

gr

[Shilpa Jain]

Thanks P90K. My problem is resolved. I used a loop to run it :)

Regards,
Shilpa

[pr12345]

is there any vb code in excel to autosave filename taking from any of the ecel cells.