Macro to replace file format

[#1]

Hi,

I am trying to replace file format from xlsx to xls using macros. Since I hardly know VBA, I need help.

the code I am using is:

Private Sub CommandButton1_Click()

Dim myfile As String

With Application.FileDialog(msoFileDialogFolderPicker)

.AllowMultiSelect = False

If .Show = -1 Then

folderName = .SelectedItems(1)

End If

End With

myfile = vFile

Do While myfile <> ""

Workbooks.OpenText Filename:= myfile

'save as excel file

ActiveWorkbook.SaveAs Filename:=folderName & "\" & Replace(myfile, ".xlsx", ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

myfile = Dir

Loop

End Sub

Here, vFile is the variable that contains entire file path, which is coming from a for each loop in the main script. I don't know if we can call this variable here.

[Peter_Cammaert]

Replace

  myFile = vFile

with

  SET var = ActiveDocument.GetVariable("vFile")

  myFile = var.GetContent.String

Best,

Peter

[#3]

Thanks for the reply Peter. Could you also pls explain, how to call the macro and what would be it's name?

Also, there comes a pop saying Macro parse failed. Any help on this?

[CELAMBARASAN]

Sometimes Qlik VBA functions works without Set keyword

var = ActiveDocument.GetVariable("vFile")

myFile = var.GetContent.String

[Peter_Cammaert]

Macros are called by way of a QlikView concept called "Action". An action is executed whenever triggered by an Event.

Actions can be added to certain QV objects that are not by default click-sensitive, like a Text box or a Button. In Object Properties you'll find a tab called Actions where you can assign o,ne or more actions to be executed in sequence whenever this object is clicked.

My guess is that you want to add a button to your sheet, that calls this macro when clicked. To accomplish this, do the following:

1. Add a button object and open its properties
2. On the General tab, change the button text into something that explains its purpose
3. On the Actions tab, click Add, select Action Type = External and in the list box on the right select Run Macro and click OK.
4. In the Macro Name-field that pops up, enter the macro text between  Private Sub and the parentheses. That's the name of the macro.
5. Close the dialogs and click the button to test its operation.

Best,

Peter

[#6]

This is what I typed in the Run Macro box:

Private Sub CLICK()

Dim myfile As String

With Application.FileDialog(msoFileDialogFolderPicker).AllowMultiSelect = False

If .Show = -1 Then

End If

End With

SET var = ActiveDocument.GetVariable("vFile")

myFile = var.GetContent.String

Do While myfile <> ""

Workbooks.OpenText Filename:= myfile

ActiveWorkbook.SaveAs Filename:= Replace(myfile, ".xlsx", ".xls")

Application.DisplayAlerts = False

ActiveWorkbook.Close

Application.DisplayAlerts = True

Loop

End Sub

But the code doesn't seem to work. Nothing happens.

[Peter_Cammaert]

The macro definition itself should go somewhere else. In the main Desktop menu, select Tools->Edit Module... The macro editor will open up. Paste the macro code in the window on the right, possibly after any definitions that are already present. When you click OK, the macro will be stored and definition will be complete. From now on, you can use this piece of VBA code by simply specifying its name.

Now go back to your Run Macro action and just enter the macro name in the Macro Name-field.

[Anonymous]

Hi

Try this this may help you

sub test

vFile="C:\temp\test.xlsx"

Set Obj_XLApp = CreateObject ("Excel.Application")

  Obj_XLApp.Visible = false

  Obj_XLApp.DisplayAlerts = false

  save_as_File_Name=left(vFile,len(vFile)-1)

  'msgbox save_as_File_Name

  set Obj_XLTemplate = Obj_XLApp.Workbooks.open (vFile)

  Obj_XLTemplate.saveas (save_as_File_Name)

  Obj_XLTemplate.close

  call sbDeletetingAFile(vFile)

  Obj_XLApp.Visible = true

  Obj_XLApp.DisplayAlerts = true

set Obj_XLTemplate=nothing

Set Obj_XLApp = nothing

end sub

Sub sbDeletetingAFile(sFile)

Set FSO = CreateObject("Scripting.FileSystemObject")

FSO.DeleteFile sFile, True

End Sub

Regards

Harsha