Creating a form from exitsing docs

Question

I have been set a bit of a task, and as everyone says, I'm relativley new to this. I've scoured everywhere for anwers to these bits, so please excvuse if I have missed anything. I have tried.

So the basis of the task -

A client facing order from (xlsx). They populate the doc as required.
Once the form is complete they click an embedded button, fire off a macro that converts the form to the correct format (CSV) and layout for uploading.

I have the below so far, but am having various issues which seem to be changing???????. I'll explain more don't worry -

Code-

 Sub ButtonMacro()
'Hide alerts
application.displayalerts = False
'
' Macro8 Macro
'

'Save to users device
    ChDir "U:\WINDOWS"
    ActiveWorkbook.SaveAs Filename:="U:\WINDOWS\OrderForm.xlsx", FileFormat:= _
        xlOpenXMLWorkbook, CreateBackup:=False
'Open new workbook
 strFileName = "U:\OrderForm.CSV"

Set objExcel = CreateObject("Excel.Application")
objExcel.Visible = True

Set objWorkbook = objExcel.Workbooks.Add()
objWorkbook.SaveAs (strFileName)

objExcel.Quit



'Create new workbook and populate
    Workbooks.Add
    ActiveCell.FormulaR1C1 = "MSG"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R[1]C"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R[1]C[3]"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "1400008000"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "501346009175"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "=Now()"
    Selection.NumberFormat = "[$-x-systime]h:mm:ss AM/PM"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "HDR"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "C"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "1400011281"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!!R[1]C[3])"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!!R2C4"
    Range("S1").Select
    ActiveCell.FormulaR1C1 = "STD"
    Range("T1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R5C2"
    Range("V1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R7C2"
    Range("W1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R8C2"
    Range("Y1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R9C2"
    Range("Z1").Select
    ActiveCell.FormulaR1C1 = "=[OrderForm.xlsx]Order!R12C2"
    Range("AB1").Select
    ActiveCell.FormulaR1C1 = "=POS"
    Range("AE1").Select
    ActiveCell.FormulaR1C1 = "=Row"
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "U:\WINDOWS\=[OrderForm.xlsx]Order!R15C3"
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "U:\WINDOWS\=[OrderForm.xlsx]Order!R15C1"
    Range("AH2").Select
    ActiveCell.FormulaR1C1 = "U:\WINDOWS\=[OrderForm.xlsx]Order!R15C2"
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "U:\WINDOWS\=[OrderForm.xlsx]Order!R15C5"
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "U:\WINDOWS\=[OrderForm.xlsx]Order!R15C7"
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "GBP"
    Range("AM1").Select
    ActiveCell.FormulaR1C1 = "TRA"
    Range("AP1").Select
    ActiveCell.FormulaR1C1 = "=COUNTIF(C[-3], ""POS"")+COUNTIF(C    [-3], ""HDR"")"


'Reinstate alerts
application.displayalerts = True
End Sub

Ok, so My thinking was to save a copy to a common drive C\TEMP (due to bussiness restrictions I have to right to a shared drive to test U:).

Once written there I would then simply open a doc and map out the correct columns. HOWEVER, this is where it all goes a bit wrong.

You will notice I have some full and some partial file paths, this is because I was testing. If I record a macro of saving the doc and use that I get the partial file path, this then opens the saved doc and doesn't continue. If I use the full path I get the first "MSG" in the top left box, then it stops.

All day I have been getting differing results.

There is probably a far neater way of doing this but I simply don't know it.

A few things to consider -

We don't know what the available file path will always be, so I have simply gone for (or will go for) C:\windows emp. If we can write straight to their desk top without knowing the path that would be the best thing.

The doc being saved is only for referrencing of the cells to map the new form out. So really it isn't veven needed, but this is the only way I know how to map the cells (a static document location).

Everything from "Save to users device to "objExcel.Quit" is stuyff I've grabbed on line to try opening a new doc over the top of the open "orderform.xlsx" so I could try using the short file path instead. Again no joy.

That is a bit of a ramble. To summarise it.

One doc should map to another, but the file paths of the doc are not being accepted, even though I have gone as far as recording them to ensure they match and they do.

The starting customer form is xlsx, but the final mapped version should be a CSV doc.

Any advice or pointing to supporting docs would really be appreciated.

Creating a form from exitsing docs

Answers (1)

Related Questions